Should We Do Something With Core Field SQL Storage?

Drupal field was part of the Drupal core since version 7. The Field extends her ability to build different kinds of systems. Since it is basic units of each entity, it is one of the most important parts of the open source software. But, when it comes to the efficiency of using SQL storage engine, the field can still do better with efficiency. I sincerely believe that we may not afford to ignore it. Let put it under a microscope had a close look at field SQL storage.

Case study:

I had built a patient scheduling system for a couple clinic offices. The project itself is not complicated. I have attached the patient profile picture on this article. We built a patient profile node type on the form. It is not a complicated form, but there are over 40 fields. It is not difficult to set up a nice patient profile node form. I also created appointment node type that connected patient profile and doctor profile with entity reference fields. Using views with exposed filter for the various reports.

It was the project where I find the issue. I am a little bit uncomfortable after I take a close look at the database. Each field has two almost identical tables. I think fields took too much unnecessary database space. I have dumped one of the fields database information to explain my concern.

1) Base table: field_data_field_initial

+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| entity_type          | varchar(128)     | NO   | PRI |         |       |
| bundle               | varchar(128)     | NO   | MUL |         |       |
| deleted              | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id            | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id          | int(10) unsigned | YES  | MUL | NULL    |       |
| language             | varchar(32)      | NO   | PRI |         |       |
| delta                | int(10) unsigned | NO   | PRI | NULL    |       |
| field_initial_value  | varchar(255)     | YES  |     | NULL    |       |
| field_initial_format | varchar(255)     | YES  | MUL | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+

Base table SQL script:

CREATE TABLE `field_data_field_initial` (
`entity_type` varchar(128) NOT NULL DEFAULT '',
`bundle` varchar(128) NOT NULL DEFAULT '',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`entity_id` int(10) unsigned NOT NULL,
`revision_id` int(10) unsigned DEFAULT NULL,
`language` varchar(32) NOT NULL DEFAULT '',
`delta` int(10) unsigned NOT NULL,
`field_initial_value` varchar(255) DEFAULT NULL,
`field_initial_format` varchar(255) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_initial_format` (`field_initial_format`)

2) Revision table: field_revision_field_initial

+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| entity_type          | varchar(128)     | NO   | PRI |         |       |
| bundle               | varchar(128)     | NO   | MUL |         |       |
| deleted              | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id            | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id          | int(10) unsigned | NO   | PRI | NULL    |       |
| language             | varchar(32)      | NO   | PRI |         |       |
| delta                | int(10) unsigned | NO   | PRI | NULL    |       |
| field_initial_value  | varchar(255)     | YES  |     | NULL    |       |
| field_initial_format | varchar(255)     | YES  | MUL | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+

Revision table SQL script:

CREATE TABLE `field_revision_field_initial` (
  `entity_type` varchar(128) NOT NULL DEFAULT '',
  `bundle` varchar(128) NOT NULL DEFAULT '',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `entity_id` int(10) unsigned NOT NULL,
  `revision_id` int(10) unsigned NOT NULL,
  `language` varchar(32) NOT NULL DEFAULT '',
  `delta` int(10) unsigned NOT NULL,
  `field_initial_value` varchar(255) DEFAULT NULL,
  `field_initial_format` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`entity_type`,`entity_id`,`revision_id`,`deleted`,`delta`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `revision_id` (`revision_id`),
  KEY `language` (`language`),
  KEY `field_initial_format` (`field_initial_format`)

Here are my concerns.

1) Normalization.

Here is one of the fields' data record.

+-------------+--------+---------+-----------+-------------+----------+-------+---------------------+----------------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_initial_value | field_initial_format |
+-------------+--------+---------+-----------+-------------+----------+-------+---------------------+----------------------+
| node        | patient_profile      |       0 |      1497 |        1497 | und      |     0 | w                   | plain_text        |
+-------------+--------+---------+-----------+-------------+----------+-------+---------------------+----------------------+

We have value "W" in the Initial field. One character took 51 bytes for storage that had not included index yet. It took another 51 byte in the revision table and more for index. In this case here, only less than two percents of space are used for real data the initial 'W', and over 98% of space is for other purposes.

For the sake of space, I think we should not use varchar for entity_type, bundle, language, field_format column. Use small int, tiny int or intÎÎ that will only take one to four bytes. The field is a basic unit of a Drupal website. A medium website can hold millions of fields. Saved one byte is equal to multiple megabytes in precious MySQL database.

2) Too complicated primary key

Each field table has a complicated primary key. Base table use `entity_type`, `entity_id`, `deleted`, `delta`, `language` as primary key. Revision table use `entity_type`, `entity_id`, `revision_id`, `deleted`, `delta`, `language` as primary key. "In InnoDB, having a long PRIMARY KEY wastes a lot of disk space because its value must be stored with every secondary index record."ÎÎÎ. It may be worthy to add an auto increasing int as a primary key.

3) Not needed field column

I found bundle type column is not necessary. We can have the system running well without bundle type column. In my clinic project, I named the node type "patient profile". The machine name patient_profile appears in each field record's bundle type column. As varchar (255), it uses 16 bytes for each table record. Let do a quick calculation. if there are 100, 000 nodes and each node have 40 fields, 100,000 x 40 x 2 x 16 = 122MB are taken for this column. Or at least, we use 2 bytes small int that will take only one-eighth of the spaces.

4) Just use revision table.

Remove one of the field's data tables. It may need a little bit more query power to get field data, but it save time when we insert, update and delete field's data. By doing so, we maintain one less table per field, edit content faster. It helps to bring better editor experience and to save on database storage space.

A contributed module field_sql_leanÎÎ addressed some of the concerns here. It still needs a lot of work on itself and if we want other contributed modules compatible with it. After all, it changed the field table structure.

Reference:

1: http://dev.mysql.com/doc/refman/5.1/en/integer-types.html
2: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
3: Field SQL storage lean solution
4: Patient profile form:medical form

Comments

free online casino ukvkw

spirit mountain casino <a href="https://onlinecasinoplayslots.us.org/">zone online casino</a> chumba casino <a href="https://onlinecasinoplayslots.us.org/">parx casino</a> | https://onlinecasinoplayslots.us.org/ - hollywood online casino https://onlinecasinoplayslots.us.org/ - casino near me

free slots online no download no registration khocb

hollywood casino free slots <a href="https://playslotscasino.fun/">charlestown races and slots</a> wizard of oz slots <a href="https://playslotscasino.fun/">slots games free</a> | https://playslotscasino.fun/ - slots online https://playslotscasino.fun/ - free slots online no download no registration

bigfish casino online games tmxmw

online gambling sites <a href="https://casinorealmoney.fun/">cashman casino</a> jackpot party casino <a href="https://casinorealmoney.fun/">hampton beach casino ballroom</a> | https://casinorealmoney.fun/ - online casino bonus https://casinorealmoney.fun/ - usa online casino

free slots online rrcml

hearts of vegas free slots <a href="https://myslotsbuddy.com/">casino slots</a> online slots free <a href="https://myslotsbuddy.com/">free slots</a> | https://myslotsbuddy.com/ - gamepoint slots https://myslotsbuddy.com/ - gold fish casino slots

pop slots repnz

slots online <a href="https://onlineslotgames.tools/">free slots hollywood</a> myvegas slots <a href="https://onlineslotgames.tools/">slots lounge</a> | https://onlineslotgames.tools/ - free slot games https://onlineslotgames.tools/ - free penny slots no download

posh casino qcbeh

pechanga casino <a href="https://online-casinos.icu/">soaring eagle casino</a> empire city casino online <a href="https://online-casinos.icu/">slot machines</a> | https://online-casinos.icu/ - twin river casino https://online-casinos.icu/ - online casino

buffalo gold slots vurph

casino slots free games <a href="https://online-slots.fun/">free games online slots</a> celebrity slots <a href="https://online-slots.fun/">house of fun free slots</a> | https://online-slots.fun/ - free slot games with no download https://online-slots.fun/ - buffalo gold slots

slots games vizgs

hollywood casino online slots <a href="http://freeslots.icu/">ruby slots</a> vegas slots <a href="http://freeslots.icu/">wheel of fortune slots</a> | http://freeslots.icu/ - online casino slots no download http://freeslots.icu/ - slots games free

jackpot party casino facebook hdphu

lady luck online casino <a href="https://onlinecasinoplay24.us.org/">slotomania slot machines</a> gsn casino on facebook <a href="https://onlinecasinoplay24.us.org/">treasure island casino minnesota</a> | https://onlinecasinoplay24.us.org/ - downstream casino https://onlinecasinoplay24.us.org/ - harrah online casino

vegas casino slots ghofc

casino slots <a href="https://casinoslots.tools/">free slots vegas</a> wizard of oz slots <a href="https://casinoslots.tools/">free slots casino games</a> | https://casinoslots.tools/ - vegas world free slots https://casinoslots.tools/ - vegas slots free

charlestown races and slots jzumr

scatter slots <a href="https://onlineslots.tools/">best online slots</a> konami free slots <a href="https://onlineslots.tools/">free online casino slots</a> | https://onlineslots.tools/ - igt free slots https://onlineslots.tools/ - play free slots

northern quest casino qnrvv

casinos online <a href="https://online-casino.fun/">red rock casino</a> real casino <a href="https://online-casino.fun/">online casino</a> | https://online-casino.fun/ - gun lake casino https://online-casino.fun/ - usa online casino

zone online casino hopwu

big fish casino slots <a href="https://online-casino-games.fun/">foxwoods online casino login</a> free online casino <a href="https://online-casino-games.fun/">free casino games</a> | https://online-casino-games.fun/ - choctaw casino https://online-casino-games.fun/ - online casinos

slots of vegas suqmx

las vegas free slots <a href="https://casinoslot.icu/">world class casino slots</a> online casino slots <a href="https://casinoslot.icu/">slots free online</a> | https://casinoslot.icu/ - caesars slots https://casinoslot.icu/ - caesars free slots online

free slot play no download qwodc

slotomania on facebook <a href="https://playslots.icu/">caesars slots</a> free vegas slots online <a href="https://playslots.icu/">casino slots free games</a> | https://playslots.icu/ - vegas slots online free https://playslots.icu/ - slots of vegas

zone online casino games rhwle

hampton beach casino ballroom <a href="https://onlinecasinogames.fun/">sugarhouse casino online</a> best online casinos <a href="https://onlinecasinogames.fun/">seneca niagara casino</a> | https://onlinecasinogames.fun/ - play casino https://onlinecasinogames.fun/ - hampton beach casino ballroom

borgata hotel casino mbgpq

free online casino games <a href="https://onlinecasinogamesy.us.org/">winstar casino oklahoma</a> hyper casinos <a href="https://onlinecasinogamesy.us.org/">big fish casino</a> | https://onlinecasinogamesy.us.org/ - lincoln casino https://onlinecasinogamesy.us.org/ - hollywood casino play4fun

free vegas slots online casino alkhk

pechanga casino <a href="https://onlinecasinoasti.com/">foxwoods casino</a> casino games slots free <a href="https://onlinecasinoasti.com/">hyper casinos</a> | https://onlinecasinoasti.com/ - winstar casino oklahoma https://onlinecasinoasti.com/ - oxford casino

winstar world casino pfqpy

empire city online casino <a href="https://onlinecasino.us.org/">play casino</a> slots for real money <a href=" https://onlinecasino.us.org/">parx online casino</a> | https://onlinecasino.us.org/ - mgm online casino https://onlinecasino.us.org/ - play casino

brian christopher slots kypts

scatter slots <a href="https://playcasinoslots.icu/">free games online slots</a> free slots games <a href="https://playcasinoslots.icu/">free slots machines</a> | https://playcasinoslots.icu/ - doubledown casino free slots https://playcasinoslots.icu/ - house of fun free slots

old vegas slots chnye

slotomania free slots <a href="https://playslots.tools/">slots free games</a> free slots vegas world <a href="https://playslots.tools/">gsn casino slots</a> | https://playslots.tools/ - free slot games with no download https://playslots.tools/ - free slot play

free online slots no download no registration mdmuw

penny slots free online <a href="https://casinoslotsparty.com/">vegas casino slots</a> free slots online no download <a href="https://casinoslotsparty.com/">mohegan sun free online slots</a> | https://casinoslotsparty.com/ - jackpot magic slots https://casinoslotsparty.com/ - da vinci diamonds free online slots

northern quest casino nxjzw

pala casino <a href="https://casinoplay.fun/">huuuge casino</a> prairie meadows casino <a href="https://casinoplay.fun/">island view casino</a> | https://casinoplay.fun/ - grand falls casino https://casinoplay.fun/ - slot machines

firekeepers casino njnph

casino real money <a href="https://playonlinecasino.us.org/">play online casino</a> casino online | https://playonlinecasino.us.org/ - casino bonus

charlestown races and slots fagjn

free online slots no download no registration <a href="https://slotsonline.tools/">free casino slot games</a> hollywood casino free slots online <a href="https://slotsonline.tools/">vegas world free slots</a> | https://slotsonline.tools/ - hollywood slots https://slotsonline.tools/ - free slot play no download

free casino slots csxjq

house of fun slots <a href="https://casino-slots.fun/">hot shot casino slots</a> play free slots <a href="https://casino-slots.fun/">free vegas slots</a> | https://casino-slots.fun/ - free games online slots https://casino-slots.fun/ - pop slots casino

san manuel online casino nignx

borgata hotel casino <a href="https://usaonlinecasinogames.us.org/">casino play</a> mystic lake casino <a href="https://usaonlinecasinogames.us.org/">hyper casinos</a> | https://usaonlinecasinogames.us.org/ - pala casino online https://usaonlinecasinogames.us.org/ - jackpot party casino facebook

cafe casino online hswuz

parx online casino <a href="https://casinoo.icu/">greektown casino</a> mohegan sun online casino <a href="https://casinoo.icu/">high 5 casino</a> | https://casinoo.icu/ - casino online https://casinoo.icu/ - winstar casino oklahoma

slots games free htzif

vegas free slots online <a href="https://slotswall.com/">mirrorball slots</a> quick hits free slots <a href="https://slotswall.com/">quick hit slots</a> | https://slotswall.com/ - free games for casino slots hollywood https://slotswall.com/ - penny slots free online

slots free nvoez

free slot machine games <a href="https://playcasinoslots.fun/">free slots games online</a> heart of vegas free slots <a href="https://playcasinoslots.fun/">free slot games</a> | https://playcasinoslots.fun/ - free penny slots https://playcasinoslots.fun/ - ruby slots

infinity slots jydjt

mirrorball slots <a href="https://freeonlineslots.icu/">free slots games</a> free slots with no download or registration <a href="https://freeonlineslots.icu/">goldfish casino slots free</a> | https://freeonlineslots.icu/ - free casino slots https://freeonlineslots.icu/ - free slot play no download

online casino slots blnmj

free casino slots games <a href="https://vegascasino.tools/">las vegas free slots</a> slotomania free slots <a href="https://vegascasino.tools/">penny slots</a> | https://vegascasino.tools/ - wizard of oz slots https://vegascasino.tools/ - slots free online

hollywood casino free slot play jvwew

free slots online <a href="https://slotsgames.icu/">free vegas slots online casino</a> play free slots for fun <a href="https://slotsgames.icu/">slots online free</a> | https://slotsgames.icu/ - buffalo gold slots https://slotsgames.icu/ - celebrity slots

jackpot party casino slots mlnwr

igt free slots <a href="https://slotsgameshub.com/">play free slots</a> vegas casino slots <a href="https://slotsgameshub.com/">slot games</a> | https://slotsgameshub.com/ - vegas casino slots https://slotsgameshub.com/ - free slots online no download no registration

virgin online casino wwdhp

online casinos for us players <a href="http://online-games.icu/">slotomania slot machines</a> soaring eagle casino <a href="http://online-games.icu/">mountaineer casino</a> | http://online-games.icu/ - hollywood casino online http://online-games.icu/ - casino near me

slots for real money piggl

vegas casino games <a href="http://online-casino.icu/">virgin casino online nj</a> online gambling sites <a href="http://online-casino.icu/">vegas casino online</a> | http://online-casino.icu/ - virgin casino online nj http://online-casino.icu/ - free online casino slots

cleopatra slots ffqyc

cashman casino slots <a href="https://slotsgamesgiant.com/">slots free games</a> slotomania on facebook <a href="https://slotsgamesgiant.com/">free slots games</a> | https://slotsgamesgiant.com/ - free vegas slots online https://slotsgamesgiant.com/ - online casino slots no download

free online casino slots lnlxv

mohegan sun free online slots <a href="https://slotsonline.fun/">free slots no download no registration</a> play slots online <a href="https://slotsonline.fun/">hearts of vegas free slots</a> | https://slotsonline.fun/ - free slots hollywood https://slotsonline.fun/ - caesars slots

grand falls casino cuuak

river spirit casino tulsa <a href="https://onlinecasinoslotsmaxx.com/">twin river casino</a> dakota sioux casino <a href="https://onlinecasinoslotsmaxx.com/">three rivers casino</a> | https://onlinecasinoslotsmaxx.com/ - usa online casino https://onlinecasinoslotsmaxx.com/ - jackpot party casino facebook

best online casino cwuam

doubledown casino <a href="https://onlinecasinoplayslots.us.org/">caesars casino online</a> vegas casino games <a href="https://onlinecasinoplayslots.us.org/">jack online casino</a> | https://onlinecasinoplayslots.us.org/ - prairie meadows casino https://onlinecasinoplayslots.us.org/ - codeshareonline doubledown casino

slots farm oxrks

gsn casino slots <a href="https://playslots.icu/">goldfish casino slots free</a> buffalo gold slots <a href="https://playslots.icu/">online casino slots no download</a> | https://playslots.icu/ - slotomania on facebook https://playslots.icu/ - wizard of oz slots

slots online free xmnef

free vegas slots online <a href="https://myslotsbuddy.com/">pch slots tournament</a> vegas world free slots <a href="https://myslotsbuddy.com/">real casino slots</a> | https://myslotsbuddy.com/ - wizard of oz slots https://myslotsbuddy.com/ - jackpot magic slots

scatter slots xhhlw

cashman casino slots free <a href="https://playslotscasino.fun/">free slots casino games</a> free casino slots no download <a href="https://playslotscasino.fun/">cashman casino slots free</a> | https://playslotscasino.fun/ - free online slots no download https://playslotscasino.fun/ - pch slots

doubledown casino facebook btvcd

online slot machines <a href="https://onlinecasino888.us.org/">doubledown casino facebook</a> casino games <a href="https://onlinecasino888.us.org/">pala casino</a> | https://onlinecasino888.us.org/ - casino online https://onlinecasino888.us.org/ - doubleu casino on facebook

red rock casino tfwyb

bovada casino <a href="https://casino-games.icu/">mystic lake casino</a> vegas casino online <a href="https://casino-games.icu/">penny slots</a> | https://casino-games.icu/ - posh casino online https://casino-games.icu/ - pechanga casino

parx casino online fjmvw

big fish casino <a href="https://online-casinos.icu/">jack online casino</a> bonus casino <a href="https://online-casinos.icu/">pala casino online nj</a> | https://online-casinos.icu/ - turning stone online casino https://online-casinos.icu/ - gambling sites

doubledown casino facebook miiiv

mystic lake casino <a href="https://onlinecasinowp.us.org/">rivers casino</a> casino slot <a href="https://onlinecasinowp.us.org/">pala casino</a> | https://onlinecasinowp.us.org/ - sugarhouse casino https://onlinecasinowp.us.org/ - hard rock casino tampa

real casino chfxl

real money casino <a href="https://casinorealmoney.fun/">maryland live casino online</a> borgata hotel casino <a href="https://casinorealmoney.fun/">fortune bay casino</a> | https://casinorealmoney.fun/ - empire city casino https://casinorealmoney.fun/ - casino near me

casino play aquzk

gsn casino <a href="https://playonlinecasino.us.org/">casino bonus</a> real money casino | https://playonlinecasino.us.org/ - casino play

best online casino eeraq

gsn casino games <a href="https://onlinecasinoasti.com/">greektown casino</a> pala casino online <a href="https://onlinecasinoasti.com/">online casino reviews</a> | https://onlinecasinoasti.com/ - casino slots https://onlinecasinoasti.com/ - play casino

Pages

Add new comment