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

real casino slots bjyct

casino play <a href="https://casinoplay.fun/">slot machines</a> casino games <a href="https://casinoplay.fun/">bovada blackjack</a> | https://casinoplay.fun/ - hollywood casino online slots https://casinoplay.fun/ - high 5 casino

grand falls casino sqeyt

virgin online casino <a href="https://onlinecasinogamesy.us.org/">seneca niagara casino</a> fortune bay casino <a href="https://onlinecasinogamesy.us.org/">free online casino</a> | https://onlinecasinogamesy.us.org/ - playmgm nj casino online https://onlinecasinogamesy.us.org/ - mohegan sun online casino

slots online pnpqg

free online casino slots <a href="https://playcasinoslots.icu/">goldfish casino slots free</a> slots games free <a href="https://playcasinoslots.icu/">hollywood casino free slots online</a> | https://playcasinoslots.icu/ - slots games free https://playcasinoslots.icu/ - slots of vegas

seneca allegany casino axtpe

thunder valley casino <a href="https://online-casino.fun/">lady luck online casino</a> best online casino <a href="https://online-casino.fun/">firekeepers casino</a> | https://online-casino.fun/ - online slot machines https://online-casino.fun/ - vegas world casino games

lady luck online casino zftiz

big fish casino slots <a href="https://usaonlinecasinogames.us.org/">chinook winds casino</a> downstream casino <a href="https://usaonlinecasinogames.us.org/">caesars casino</a> | https://usaonlinecasinogames.us.org/ - real casino slots https://usaonlinecasinogames.us.org/ - hollywood casino online

hollywood casino free slots zeszo

gossip slots <a href="https://casino-slots.fun/">slot games free</a> free online slots <a href="https://casino-slots.fun/">turning stone online slots</a> | https://casino-slots.fun/ - caesars free slots https://casino-slots.fun/ - my vegas slots

slot machines xwnmn

free online slots <a href="https://casinoslot.icu/">caesars free slots</a> play free slots for fun <a href="https://casinoslot.icu/">free vegas slots</a> | https://casinoslot.icu/ - free buffalo slots https://casinoslot.icu/ - mirrorball slots

free slots online huoix

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

pch slots ooqmg

free slots no download <a href="https://slotsgamesgiant.com/">online casino slots</a> free slot games online <a href="https://slotsgamesgiant.com/">free slots online no download</a> | https://slotsgamesgiant.com/ - gossip slots https://slotsgamesgiant.com/ - free video slots

vegas world free slots tcnlc

penny slots free online <a href="https://slotsonline.tools/">turning stone online slots</a> slots farm <a href="https://slotsonline.tools/">free slots no download no registration</a> | https://slotsonline.tools/ - hollywood casino online slots https://slotsonline.tools/ - liberty slots

mgm online casino nj hxpsk

double down casino <a href="https://onlinecasinoplayslots.us.org/">online casino reviews</a> casino games slots free <a href="https://onlinecasinoplayslots.us.org/">casino online</a> | https://onlinecasinoplayslots.us.org/ - oxford casino https://onlinecasinoplayslots.us.org/ - posh casino online

online casinos dtwzm

online casino bonus <a href="https://onlinecasino.us.org/">online casino slots</a> online casinos <a href=" https://onlinecasino.us.org/">gsn casino games</a> | https://onlinecasino.us.org/ - parx online casino https://onlinecasino.us.org/ - online casinos for us players

hollywood casino free slots online mltts

free slots slotomania <a href="https://myslotsbuddy.com/">slot games</a> vegas world slots <a href="https://myslotsbuddy.com/">slots of vegas casino</a> | https://myslotsbuddy.com/ - free slot games with no download https://myslotsbuddy.com/ - caesars free slots

mirrorball slots qtpsq

casino games slots free <a href="https://freeonlineslots.icu/">slotomania free slots</a> slots games free <a href="https://freeonlineslots.icu/">slots free games</a> | https://freeonlineslots.icu/ - free slot play https://freeonlineslots.icu/ - online casino slots

slot games free qnvxl

gamepoint slots <a href="https://vegascasino.tools/">penny slots</a> free online slots <a href="https://vegascasino.tools/">penny slots free online</a> | https://vegascasino.tools/ - play free slots https://vegascasino.tools/ - free slots online no download no registration

slots for real money kqhlm

best online casinos <a href="https://playonlinecasino.us.org/">free casino games</a> casino online slots | https://playonlinecasino.us.org/ - casino online slots

plainridge casino oezeo

ignition casino <a href="https://casinoo.icu/">gsn casino on facebook</a> sugarhouse casino <a href="https://casinoo.icu/">pala casino</a> | https://casinoo.icu/ - parx casino https://casinoo.icu/ - gambling sites

caesars free slots online bomxr

online casino slots no download <a href="https://playcasinoslots.fun/">online slot games</a> free online slots vegas world <a href="https://playcasinoslots.fun/">hollywood casino online slots free</a> | https://playcasinoslots.fun/ - slots farm https://playcasinoslots.fun/ - gsn casino slots

play free slots smipg

liberty slots casino <a href="https://playslotscasino.fun/">free vegas slots online casino</a> free slot machine games <a href="https://playslotscasino.fun/">hollywood casino free slots online</a> | https://playslotscasino.fun/ - caesars slots free casino https://playslotscasino.fun/ - penny slots

vegas slots ywewf

free slots no download no registration <a href="https://slotsgames.icu/">free slots slotomania</a> vegas slots online free <a href="https://slotsgames.icu/">gold fish casino slots</a> | https://slotsgames.icu/ - hollywood casino free slot play https://slotsgames.icu/ - pop slots casino

online casino slots snzut

sandia casino <a href="http://online-games.icu/">winstar casino oklahoma</a> pala casino online nj <a href="http://online-games.icu/">doubledown casino facebook</a> | http://online-games.icu/ - casino online http://online-games.icu/ - virgin casino online nj

pop slots ohjrl

gsn casino slots <a href="https://playslots.icu/">infinity slots</a> free online slots no download no registration <a href="https://playslots.icu/">casino slots free</a> | https://playslots.icu/ - free slot play https://playslots.icu/ - vegas slots online

free slots online no download no registration wtmes

hollywood casino free slots online <a href="https://slotswall.com/">vegas world slots</a> hollywood casino online slots free <a href="https://slotswall.com/">jackpot party casino slots</a> | https://slotswall.com/ - pop slots casino https://slotswall.com/ - house of fun free slots

dakota sioux casino ebazl

slot machines <a href="https://online-casinos.icu/">sugarhouse casino online</a> gsn casino <a href="https://online-casinos.icu/">huuuge casino</a> | https://online-casinos.icu/ - foxwoods online casino login https://online-casinos.icu/ - pala casino

caesars online casino mglyw

del lago casino <a href="https://onlinecasinoslotsmaxx.com/">best online casino</a> big fish casino slots <a href="https://onlinecasinoslotsmaxx.com/">hollywood online casino</a> | https://onlinecasinoslotsmaxx.com/ - high five casino slots https://onlinecasinoslotsmaxx.com/ - morongo casino

play online casino wnuxn

sandia casino <a href="https://onlinecasino888.us.org/">grand falls casino</a> choctaw casino <a href="https://onlinecasino888.us.org/">rivers casino pittsburgh</a> | https://onlinecasino888.us.org/ - free online casino https://onlinecasino888.us.org/ - hyper casinos

free games for casino slots hollywood huhyj

slot machine games free <a href="https://slotsonline.fun/">old vegas slots</a> house of fun free slots <a href="https://slotsonline.fun/">vegas slots</a> | https://slotsonline.fun/ - free buffalo slots https://slotsonline.fun/ - free slots online

free slot games online khyfy

free vegas slots <a href="https://casinoslotsparty.com/">free penny slots online</a> play slots <a href="https://casinoslotsparty.com/">ruby slots</a> | https://casinoslotsparty.com/ - free slots slotomania https://casinoslotsparty.com/ - infinity slots

sugarhouse casino otfyg

viejas casino <a href="https://casinorealmoney.fun/">mohegan sun casino</a> doubleu casino on facebook <a href="https://casinorealmoney.fun/">online casino gambling</a> | https://casinorealmoney.fun/ - lincoln casino https://casinorealmoney.fun/ - stn play online casino

doubledown casino free slots pczih

caesars free slots online <a href="https://onlineslots.tools/">free games online slots</a> free slots no download <a href="https://onlineslots.tools/">free slots games</a> | https://onlineslots.tools/ - world class casino slots https://onlineslots.tools/ - jackpot party casino slots

cherokee casino xvwvm

casinos online <a href="https://onlinecasinoasti.com/">doubledown casino</a> casino blackjack <a href="https://onlinecasinoasti.com/">soaring eagle casino</a> | https://onlinecasinoasti.com/ - casino bonus https://onlinecasinoasti.com/ - virgin casino online

pch slots ehsyh

free penny slots no download <a href="http://freeslots.icu/">penny slots</a> caesars free slots online <a href="http://freeslots.icu/">hot shot casino slots</a> | http://freeslots.icu/ - vegas slots http://freeslots.icu/ - free buffalo slots

winstar casino oklahoma koijo

hard rock casino tampa <a href="https://casino-games.icu/">slot machines</a> river spirit casino tulsa <a href="https://casino-games.icu/">muckleshoot casino</a> | https://casino-games.icu/ - ignition casino https://casino-games.icu/ - northern quest casino

free buffalo slots nwyux

slots games free <a href="https://onlineslotgames.tools/">free vegas slots online casino</a> play slots <a href="https://onlineslotgames.tools/">turning stone online slots</a> | https://onlineslotgames.tools/ - pch slots https://onlineslotgames.tools/ - free vegas slots online

empire city casino cdxnf

casinos online <a href="http://online-casino.icu/">foxwoods online casino login</a> casinos in iowa <a href="http://online-casino.icu/">gsn casino on facebook</a> | http://online-casino.icu/ - lincoln casino http://online-casino.icu/ - real money casino

free slots casino games xgfkx

free online slots no download no registration <a href="https://playslots.tools/">goldfish casino slots free</a> slots of vegas casino <a href="https://playslots.tools/">free buffalo slots</a> | https://playslots.tools/ - free games for casino slots hollywood https://playslots.tools/ - slots free

online casino games veram

belterra casino <a href="https://onlinecasinowp.us.org/">real casino slots</a> real money casino <a href="https://onlinecasinowp.us.org/">fortune bay casino</a> | https://onlinecasinowp.us.org/ - hollywood casino play4fun https://onlinecasinowp.us.org/ - empire city online casino

free slots online no download zdxyo

hearts of vegas free slots <a href="https://playslots.icu/">online slot games</a> slotomania free slots <a href="https://playslots.icu/">house of fun free slots</a> | https://playslots.icu/ - penny slots https://playslots.icu/ - vegas world free games online slots

free online slots no download gvtld

slots for free <a href="https://online-slots.fun/">penny slots</a> slots lounge <a href="https://online-slots.fun/">free slots</a> | https://online-slots.fun/ - slot machines https://online-slots.fun/ - slot machines

play casino swhvx

casino play <a href="https://onlinecasinogames.fun/">mohegan sun online casino</a> doubleu casino on facebook <a href="https://onlinecasinogames.fun/">bonus casino</a> | https://onlinecasinogames.fun/ - hampton beach casino ballroom https://onlinecasinogames.fun/ - online casino games

double down casino uyomq

slotomania slot machines <a href="https://online-casino-games.fun/">hampton beach casino ballroom</a> best online casino <a href="https://online-casino-games.fun/">jackpot party casino</a> | https://online-casino-games.fun/ - rock n cash casino slots https://online-casino-games.fun/ - play casino

gsn casino games bqobg

mgm online casino <a href="https://playonlinecasino.us.org/">online casino bonus</a> gsn casino games | https://playonlinecasino.us.org/ - online casinos for us players

free slots online no download ojcpk

play slots online <a href="https://slotsgameshub.com/">free casino slots no download</a> cashman casino slots <a href="https://slotsgameshub.com/">online slots free</a> | https://slotsgameshub.com/ - free casino slots games https://slotsgameshub.com/ - slots lounge

turning stone online casino wpcie

online gambling casino <a href="https://onlinecasinoplay24.us.org/">del lago casino</a> grand falls casino <a href="https://onlinecasinoplay24.us.org/">free casino</a> | https://onlinecasinoplay24.us.org/ - usa online casino https://onlinecasinoplay24.us.org/ - spirit mountain casino

charlestown races and slots egmst

my vegas slots <a href="https://playcasinoslots.icu/">gold fish casino slots</a> free slot games online <a href="https://playcasinoslots.icu/">gsn casino slots</a> | https://playcasinoslots.icu/ - vegas slots online free https://playcasinoslots.icu/ - caesars free slots online

hampton beach casino ballroom xysem

treasure island casino minnesota <a href="https://casinoplay.fun/">doubleu casino on facebook</a> caesars casino <a href="https://casinoplay.fun/">choctaw casino durant oklahoma</a> | https://casinoplay.fun/ - online gambling sites https://casinoplay.fun/ - island view casino

online slots free iafku

free slots with no download or registration <a href="https://casinoslots.tools/">penny slots free online</a> heart of vegas free slots <a href="https://casinoslots.tools/">free slots online no download</a> | https://casinoslots.tools/ - free slot play no download https://casinoslots.tools/ - scatter slots

gsn casino slots udtgq

buffalo slots <a href="https://slotsgamesgiant.com/">free slots machines</a> free slots no download <a href="https://slotsgamesgiant.com/">slots free games</a> | https://slotsgamesgiant.com/ - hollywood casino online slots https://slotsgamesgiant.com/ - my vegas slots

doubledown casino hgkqx

rock n cash casino slots <a href="https://usaonlinecasinogames.us.org/">grand falls casino</a> double down casino <a href="https://usaonlinecasinogames.us.org/">oxford casino</a> | https://usaonlinecasinogames.us.org/ - online casino slots https://usaonlinecasinogames.us.org/ - grand casino hinckley

choctaw casino oipsj

real casino slots <a href="https://onlinecasinogamesy.us.org/">borgata hotel casino</a> play online casino <a href="https://onlinecasinogamesy.us.org/">slots casino games</a> | https://onlinecasinogamesy.us.org/ - stn play online casino https://onlinecasinogamesy.us.org/ - best online casino

Pages

Add new comment

Target Image