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

foxwoods casino online slots satdo

virgin casino online nj <a href="https://onlinecasinowp.us.org/">gsn casino games</a> ignition casino <a href="https://onlinecasinowp.us.org/">real casino</a> | https://onlinecasinowp.us.org/ - play casino https://onlinecasinowp.us.org/ - free casino games

big fish casino ctohz

high five casino slots <a href="https://casino-games.icu/">slotomania slot machines</a> doubleu casino on facebook <a href="https://casino-games.icu/">pechanga casino</a> | https://casino-games.icu/ - slotomania slot machines https://casino-games.icu/ - grand casino hinckley

slot machine games free htvht

free slots online <a href="https://slotswall.com/">free slots vegas</a> free vegas slots online casino <a href="https://slotswall.com/">vegas slots online</a> | https://slotswall.com/ - hollywood casino free slots online https://slotswall.com/ - slot machines

free vegas slots online hbgtk

free online slots <a href="https://playslots.icu/">online slots real money</a> online slot games <a href="https://playslots.icu/">hollywood casino free slots</a> | https://playslots.icu/ - free slots with no download or registration https://playslots.icu/ - free online slots no download

free slots ylwmu

free slots <a href="https://slotsgameshub.com/">casino slots free</a> free slots no download no registration <a href="https://slotsgameshub.com/">doubledown casino free slots</a> | https://slotsgameshub.com/ - free online slots no download no registration https://slotsgameshub.com/ - play free slots for fun

online casino bonus kjcgu

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

casino slots free games pqbmq

free slot machine games <a href="https://onlineslotgames.tools/">free games for casino slots hollywood</a> free slot play <a href="https://onlineslotgames.tools/">free slot games with no download</a> | https://onlineslotgames.tools/ - caesars free slots online https://onlineslotgames.tools/ - free online slots

blue chip casino canmg

playmgm nj casino online <a href="https://onlinecasino888.us.org/">island view casino</a> chumash casino <a href="https://onlinecasino888.us.org/">virgin casino online nj</a> | https://onlinecasino888.us.org/ - zone online casino https://onlinecasino888.us.org/ - rivers casino

foxwoods casino huhla

casinos online <a href="https://onlinecasinoplay24.us.org/">potawatomi casino</a> clearwater casino <a href="https://onlinecasinoplay24.us.org/">snoqualmie casino</a> | https://onlinecasinoplay24.us.org/ - doubledown casino https://onlinecasinoplay24.us.org/ - mystic lake casino

best online casino qhmfw

red rock casino <a href="https://online-casino.fun/">pala casino online</a> seneca niagara casino <a href="https://online-casino.fun/">spirit mountain casino</a> | https://online-casino.fun/ - vegas casino online https://online-casino.fun/ - parx casino

hearts of vegas free slots mdoya

pch slots tournament <a href="https://casinoslots.tools/">heart of vegas free slots</a> vegas slots online free <a href="https://casinoslots.tools/">caesars free slots</a> | https://casinoslots.tools/ - casino slots free games https://casinoslots.tools/ - free buffalo slots

slots free online xlvxb

free vegas slots online casino <a href="https://playslotscasino.fun/">free slots online no download no registration</a> slots farm <a href="https://playslotscasino.fun/">free slots online</a> | https://playslotscasino.fun/ - myvegas slots https://playslotscasino.fun/ - vegas world free games online slots

pch slots tournament jwkja

free slots games <a href="https://online-slots.fun/">cleopatra slots</a> casino slots free games <a href="https://online-slots.fun/">slots free games</a> | https://online-slots.fun/ - slots lounge https://online-slots.fun/ - free games for casino slots hollywood

penny slots zajyr

viejas casino <a href="https://online-casinos.icu/">virgin online casino</a> double u casino <a href="https://online-casinos.icu/">harrah cherokee casino</a> | https://online-casinos.icu/ - winstar world casino https://online-casinos.icu/ - virgin casino online

borgata hotel casino iutro

clearwater casino <a href="https://casinorealmoney.fun/">winstar casino oklahoma</a> choctaw casino durant oklahoma <a href="https://casinorealmoney.fun/">borgata hotel casino</a> | https://casinorealmoney.fun/ - oxford casino https://casinorealmoney.fun/ - river spirit casino tulsa

slot games free znafd

konami free slots <a href="http://freeslots.icu/">free slots games online</a> heart of vegas free slots <a href="http://freeslots.icu/">hollywood casino free slot play</a> | http://freeslots.icu/ - free online casino slots http://freeslots.icu/ - caesars free slots

free slot play no download mqmcv

vegas world slots <a href="https://myslotsbuddy.com/">slotomania free slots</a> free penny slots no download <a href="https://myslotsbuddy.com/">free slots online no download no registration</a> | https://myslotsbuddy.com/ - mirrorball slots https://myslotsbuddy.com/ - heart of vegas slots

slots free online cqefl

pop slots <a href="https://casinoslot.icu/">mohegan sun free online slots</a> goldfish slots <a href="https://casinoslot.icu/">free games for casino slots hollywood</a> | https://casinoslot.icu/ - free slots games https://casinoslot.icu/ - free penny slots online

slot machines mcfdt

tropicana online casino <a href="https://onlinecasinoplayslots.us.org/">online casino slots</a> free vegas slots online casino <a href="https://onlinecasinoplayslots.us.org/">double down casino</a> | https://onlinecasinoplayslots.us.org/ - doubleu casino https://onlinecasinoplayslots.us.org/ - double u casino

real casino slots eijci

pompeii slots <a href="https://onlineslots.tools/">casino slots free</a> celebrity slots <a href="https://onlineslots.tools/">free vegas slots online casino</a> | https://onlineslots.tools/ - free games for casino slots hollywood https://onlineslots.tools/ - house of fun free slots

golden nugget online casino bxmsj

seneca allegany casino <a href="https://online-casino-games.fun/">bonus casino</a> chumash casino <a href="https://online-casino-games.fun/">four winds casino</a> | https://online-casino-games.fun/ - zone online casino games https://online-casino-games.fun/ - doubleu casino on facebook

hollywood slots afdja

turning stone online slots <a href="https://playslots.icu/">vegas world free slots</a> caesars slots <a href="https://playslots.icu/">online slots</a> | https://playslots.icu/ - free penny slots online https://playslots.icu/ - online slots real money

double down casino rgxig

posh casino <a href="https://onlinecasinogames.fun/">gambling sites</a> casino slot <a href="https://onlinecasinogames.fun/">lincoln casino</a> | https://onlinecasinogames.fun/ - choctaw casino https://onlinecasinogames.fun/ - chumash casino

borgata online casino ulqjw

cashman casino <a href="https://onlinecasinogamesy.us.org/">maryland live casino online</a> real casino <a href="https://onlinecasinogamesy.us.org/">blue chip casino</a> | https://onlinecasinogamesy.us.org/ - online casino slots https://onlinecasinogamesy.us.org/ - choctaw casino

real money casino smbpt

casino online slots <a href="https://onlinecasino.us.org/">real money casino</a> bovada casino <a href=" https://onlinecasino.us.org/">online casino real money</a> | https://onlinecasino.us.org/ - free online casino slots https://onlinecasino.us.org/ - casino blackjack

jackpot party casino slots fcbve

buffalo slots <a href="https://playcasinoslots.icu/">free slots with no download or registration</a> play free slots for fun <a href="https://playcasinoslots.icu/">caesars free slots online</a> | https://playcasinoslots.icu/ - online casino slots https://playcasinoslots.icu/ - monopoly slots

riverwind casino qzmse

online casino reviews <a href="https://casinoplay.fun/">prairie meadows casino</a> caesar casino online slot games <a href="https://casinoplay.fun/">casino games</a> | https://casinoplay.fun/ - mgm online casino nj https://casinoplay.fun/ - online casino

slots free mxajs

slots farm <a href="https://playslots.tools/">buffalo slots</a> free slots no download no registration <a href="https://playslots.tools/">vegas free slots online</a> | https://playslots.tools/ - casino slots free https://playslots.tools/ - online slots free

slot games ryttt

slotomania on facebook <a href="https://slotsonline.tools/">ruby slots</a> mohegan sun free online slots <a href="https://slotsonline.tools/">free slots casino games</a> | https://slotsonline.tools/ - freeslots.com slots https://slotsonline.tools/ - hollywood casino free slot play

gossip slots ooclk

free slot games online <a href="https://casinoslotsparty.com/">da vinci diamonds free online slots</a> vegas world free games online slots <a href="https://casinoslotsparty.com/">slot machine games free</a> | https://casinoslotsparty.com/ - da vinci diamonds free online slots https://casinoslotsparty.com/ - caesars free slots online

free casino muqlw

zone online casino games <a href="https://casinoo.icu/">borgata online casino</a> tropicana online casino <a href="https://casinoo.icu/">spirit mountain casino</a> | https://casinoo.icu/ - betfair casino online nj https://casinoo.icu/ - winstar casino oklahoma

casino slots ucnkf

slots free <a href="https://casino-slots.fun/">old vegas slots</a> free penny slots <a href="https://casino-slots.fun/">free buffalo slots</a> | https://casino-slots.fun/ - penny slots https://casino-slots.fun/ - slots free online

casino blackjack htkrn

sandia casino <a href="https://onlinecasinoasti.com/">royal river casino</a> vegas casino games <a href="https://onlinecasinoasti.com/">casino slot</a> | https://onlinecasinoasti.com/ - rivers casino pittsburgh https://onlinecasinoasti.com/ - online casino games free

doubledown casino facebook boivw

real casino <a href="https://usaonlinecasinogames.us.org/">slot machines</a> hollywood casino online slots <a href="https://usaonlinecasinogames.us.org/">double down casino</a> | https://usaonlinecasinogames.us.org/ - del lago casino https://usaonlinecasinogames.us.org/ - winstar casino

free slots games rjgou

vegas free slots online <a href="https://playcasinoslots.fun/">free vegas world slots</a> free slots 777 <a href="https://playcasinoslots.fun/">free penny slots</a> | https://playcasinoslots.fun/ - mohegan sun free online slots https://playcasinoslots.fun/ - online slots free

casino play ltbwk

free online casino <a href="https://playonlinecasino.us.org/">online casino slots</a> empire city online casino | https://playonlinecasino.us.org/ - online gambling

liberty slots agzxh

ruby slots <a href="https://freeonlineslots.icu/">slotomania free slots</a> slots free games <a href="https://freeonlineslots.icu/">vegas free slots online</a> | https://freeonlineslots.icu/ - mohegan sun free online slots https://freeonlineslots.icu/ - turning stone online slots

pop slots casino egaci

slots games <a href="https://slotsgames.icu/">free slots machines</a> free slot play <a href="https://slotsgames.icu/">gamepoint slots</a> | https://slotsgames.icu/ - caesars slots https://slotsgames.icu/ - jackpot party casino slots

hollywood casino online ihayf

sandia casino <a href="http://online-casino.icu/">online casinos for us players</a> bovada casino <a href="http://online-casino.icu/">slots for real money</a> | http://online-casino.icu/ - viejas casino http://online-casino.icu/ - mgm online casino

caesars free slots online xjowx

free casino slots games <a href="https://vegascasino.tools/">free slot play</a> vegas world slots <a href="https://vegascasino.tools/">hollywood casino free slots</a> | https://vegascasino.tools/ - hollywood casino online slots free https://vegascasino.tools/ - celebrity slots

online slots ugvbe

slots of vegas casino <a href="https://slotswall.com/">igt free slots</a> free slots no download <a href="https://slotswall.com/">slotomania free slots</a> | https://slotswall.com/ - free slots online no download https://slotswall.com/ - casino slots free games

turning stone online slots umvso

slots farm <a href="https://slotsgamesgiant.com/">slots farm</a> free slots with no download or registration <a href="https://slotsgamesgiant.com/">quick hits free slots</a> | https://slotsgamesgiant.com/ - free games online slots https://slotsgamesgiant.com/ - free casino slots games

vegas slots free pprcd

free slot play no download <a href="https://slotsonline.fun/">turning stone online slots</a> da vinci diamonds free online slots <a href="https://slotsonline.fun/">real casino slots</a> | https://slotsonline.fun/ - pch slots https://slotsonline.fun/ - free online casino slots

empire city casino online afcxy

online casinos <a href="http://online-games.icu/">caesars online casino</a> treasure island casino minnesota <a href="http://online-games.icu/">free casino</a> | http://online-games.icu/ - foxwoods online casino http://online-games.icu/ - chumash casino

online slot games mjxaj

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

penny slots lujtz

snoqualmie casino <a href="https://onlinecasinoslotsmaxx.com/">empire city casino online</a> golden nugget online casino <a href="https://onlinecasinoslotsmaxx.com/">casinos online</a> | https://onlinecasinoslotsmaxx.com/ - seneca niagara casino https://onlinecasinoslotsmaxx.com/ - mgm online casino

wizard of oz slots cojze

house of fun free slots <a href="https://playslots.icu/">free games for casino slots hollywood</a> free slots casino games <a href="https://playslots.icu/">free slots with no download or registration</a> | https://playslots.icu/ - pop slots https://playslots.icu/ - pch slots

jackpot party casino jojcw

online gambling sites <a href="https://casino-games.icu/">foxwoods casino</a> winstar casino oklahoma <a href="https://casino-games.icu/">hollywood casino online slots</a> | https://casino-games.icu/ - belterra casino https://casino-games.icu/ - parx online casino

slotomania slot machines kmwtj

winstar casino <a href="https://onlinecasinowp.us.org/">snoqualmie casino</a> paradise casino <a href="https://onlinecasinowp.us.org/">northern quest casino</a> | https://onlinecasinowp.us.org/ - four winds casino https://onlinecasinowp.us.org/ - vegas world casino games

double down casino jluhp

mohegan sun online casino <a href="https://onlinecasino888.us.org/">lady luck online casino</a> hallmark casino online <a href="https://onlinecasino888.us.org/">chumash casino</a> | https://onlinecasino888.us.org/ - vegas casino online https://onlinecasino888.us.org/ - free casino games

Pages

Add new comment

Target Image