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

downstream casino szqse

free casino games no download <a href="https://playonlinecasino.icu/">turning stone online casino</a> free casino games slots <a href="https://playonlinecasino.icu/">hollywood casino</a> | https://playonlinecasino.icu/ - royal river casino https://playonlinecasino.icu/ - virgin casino online nj

caesars casino online qmqob

online casino games <a href="https://slotsonline.ooo/">foxwoods casino</a> riverwind casino <a href="https://slotsonline.ooo/">four winds casino</a> | https://slotsonline.ooo/ - belterra casino https://slotsonline.ooo/ - bigfish casino online games

liberty slots casino kmmrv

hollywood slots <a href="https://slotsrealmoney.icu/">ruby slots</a> my vegas slots <a href="https://slotsrealmoney.icu/">slot games</a> | https://slotsrealmoney.icu/ - free online slots no download https://slotsrealmoney.icu/ - hollywood casino free slots online

caesars casino online azcwp

free casino games slotomania <a href="https://realmoneycasino.ooo/">casino real money</a> foxwoods casino <a href="https://realmoneycasino.ooo/">caesar casino online slot games</a> | https://realmoneycasino.ooo/ - lady luck online casino https://realmoneycasino.ooo/ - hyper casinos

hard rock casino atlantic city evbrk

play online casino <a href="https://vegascasino.icu/">winstar world casino</a> hard rock casino atlantic city <a href="https://vegascasino.icu/">rock n cash casino slots</a> | https://vegascasino.icu/ - zone online casino https://vegascasino.icu/ - tropicana online casino

slotomania on facebook unoza

vegas world free slots <a href="https://slotonline.icu/">slots free</a> free slots no download <a href="https://slotonline.icu/">online slots</a> | https://slotonline.icu/ - pch slots https://slotonline.icu/ - free slot games with no download

plainridge casino ofirz

winstar casino oklahoma <a href="https://playslots.ooo/">hollywood online casino</a> belterra casino <a href="https://playslots.ooo/">lady luck online casino</a> | https://playslots.ooo/ - betfair casino online nj https://playslots.ooo/ - high five casino slots

lady luck online casino omrwc

online gambling <a href="https://onlinecasinoplay.us.org/">zone online casino games</a> free casino games sun moon <a href="https://onlinecasinoplay.us.org/">parx online casino</a> | https://onlinecasinoplay.us.org/ - zone online casino games https://onlinecasinoplay.us.org/ - casino online slots

winstar casino wbqne

hampton beach casino ballroom <a href="https://casinoo.ooo/">free vegas casino games</a> free casino <a href="https://casinoo.ooo/">harrah online casino</a> | https://casinoo.ooo/ - golden nugget online casino https://casinoo.ooo/ - high five casino slots

mgm online casino nj hfbxl

zone online casino games <a href="https://freeonlineslots.ooo/">sugarhouse casino</a> four winds casino <a href="https://freeonlineslots.ooo/">hard rock casino atlantic city</a> | https://freeonlineslots.ooo/ - morongo casino https://freeonlineslots.ooo/ - free casino games no download

free slots vegas wsazq

cashman casino slots free <a href="https://onlinecasinoslots.icu/">heart of vegas slots</a> heart of vegas free slots <a href="https://onlinecasinoslots.icu/">free vegas slots online</a> | https://onlinecasinoslots.icu/ - cashman casino slots free https://onlinecasinoslots.icu/ - penny slots free online

vegas slots qnpfp

free slot play no download <a href="https://realmoneyslots.icu/">cashman casino slots free</a> slotomania free slots <a href="https://realmoneyslots.icu/">infinity slots</a> | https://realmoneyslots.icu/ - slots free https://realmoneyslots.icu/ - caesars slots free casino

play free vegas casino games wjyup

chumba casino <a href="https://trstonlinecasino.com/">bovada casino</a> casino slots <a href="https://trstonlinecasino.com/">lady luck online casino</a> | https://trstonlinecasino.com/ - online casino real money https://trstonlinecasino.com/ - online casinos

casino games yobry

zone online casino games <a href="https://usacasino.ooo/">casinos near me</a> best online casinos <a href="https://usacasino.ooo/">free casino games online</a> | https://usacasino.ooo/ - treasure island casino minnesota https://usacasino.ooo/ - twin river casino

cherokee casino iusls

casino online <a href="https://freecasinogames.icu/">bovada casino</a> gsn casino slots <a href="https://freecasinogames.icu/">mountaineer casino</a> | https://freecasinogames.icu/ - free casino games slots https://freecasinogames.icu/ - online casino reviews

harrah cherokee casino wlrvu

888 casino <a href="https://realmoneycasino.icu/">lincoln casino</a> 888 casino <a href="https://realmoneycasino.icu/">empire casino online</a> | https://realmoneycasino.icu/ - caesars online casino https://realmoneycasino.icu/ - choctaw casino durant oklahoma

lady luck hscex

doubleu casino <a href="https://casino-bonus.icu/">free casino games sun moon</a> free casino games slotomania <a href="https://casino-bonus.icu/">cherokee casino</a> | https://casino-bonus.icu/ - muckleshoot casino https://casino-bonus.icu/ - borgata online casino

freeslots.com slots lfmlc

monopoly slots <a href="https://casinoslotsplay.icu/">play slots</a> free slots casino games <a href="https://casinoslotsplay.icu/">monopoly slots</a> | https://casinoslotsplay.icu/ - free slots online no download https://casinoslotsplay.icu/ - online casino slots

caesars online casino pgmrs

casino games <a href="https://onlinecasinoplay.icu/">bonus casino</a> cherokee casino <a href="https://onlinecasinoplay.icu/">hollywood casino play4fun</a> | https://onlinecasinoplay.icu/ - free casino games slots https://onlinecasinoplay.icu/ - virgin casino online nj

san manuel casino fluhi

meadows casino <a href="https://casinovegas.ooo/">free slots games</a> virgin casino online nj <a href="https://casinovegas.ooo/">vegas casino games</a> | https://casinovegas.ooo/ - pala casino online nj https://casinovegas.ooo/ - potawatomi casino

sugarhouse casino teafv

mountaineer casino <a href="https://onlinecasinox.icu/">harrah online casino</a> free casino <a href="https://onlinecasinox.icu/">online casino real money</a> | https://onlinecasinox.icu/ - no deposit casino https://onlinecasinox.icu/ - bigfish casino online games

best online slots vfwzx

play slots <a href="https://vegascasinoslots.icu/">free slots no download no registration</a> vegas slots online <a href="https://vegascasinoslots.icu/">free slots casino games</a> | https://vegascasinoslots.icu/ - free slot games with no download https://vegascasinoslots.icu/ - free buffalo slots

three rivers casino yfjke

mgm online casino nj <a href="https://freeonlinecasino.icu/">four winds casino</a> high 5 casino <a href="https://freeonlinecasino.icu/">parx casino</a> | https://freeonlinecasino.icu/ - meadows casino https://freeonlinecasino.icu/ - winstar world casino

free slot play no download mzqeb

free slots casino games <a href="https://casinoslotsonline.icu/">caesars slots free casino</a> slotomania free slots <a href="https://casinoslotsonline.icu/">free penny slots no download</a> | https://casinoslotsonline.icu/ - jackpot party casino slots https://casinoslotsonline.icu/ - free slots hollywood

casino slots wlgct

lady luck <a href="https://onlinecasino2018.us.org/">mgm online casino</a> free casino games online <a href="https://onlinecasino2018.us.org/">play online casino</a> | https://onlinecasino2018.us.org/ - real money casino https://onlinecasino2018.us.org/ - gsn casino games

turning stone casino vuaap

morongo casino <a href="https://onlinecasinorealmoney.ooo/">hard rock casino atlantic city</a> empire casino online <a href="https://onlinecasinorealmoney.ooo/">cafe casino online</a> | https://onlinecasinorealmoney.ooo/ - las vegas casinos https://onlinecasinorealmoney.ooo/ - bovada casino

mohegan sun casino lytto

seneca niagara casino <a href="https://slotsgames.ooo/">hampton beach casino ballroom</a> huuuge casino <a href="https://slotsgames.ooo/">winstar casino</a> | https://slotsgames.ooo/ - riverwind casino https://slotsgames.ooo/ - sugarhouse casino online

casino online detlj

888 casino <a href="https://onlinecasinoslots.ooo/">big fish casino</a> foxwoods casino <a href="https://onlinecasinoslots.ooo/">free casino</a> | https://onlinecasinoslots.ooo/ - greektown casino https://onlinecasinoslots.ooo/ - foxwoods casino

rivers casino schenectady vvjiq

caesars casino <a href="https://playcasinogames.icu/">riverwind casino</a> lady luck online casino <a href="https://playcasinogames.icu/">online gambling casino</a> | https://playcasinogames.icu/ - casino games https://playcasinogames.icu/ - jackpot party casino facebook

hot shot casino slots usnoe

free slots machines <a href="https://freeslots24.icu/">infinity slots</a> pch slots tournament <a href="https://freeslots24.icu/">vegas casino slots</a> | https://freeslots24.icu/ - play free slots for fun https://freeslots24.icu/ - free online slots no download

caesars online casino zggnd

lady luck online casino <a href="https://onlinecasinoo.icu/">turning stone online casino</a> zone online casino <a href="https://onlinecasinoo.icu/">best online casinos</a> | https://onlinecasinoo.icu/ - online casino games https://onlinecasinoo.icu/ - doubledown casino facebook

pala casino online zyzbm

big fish casino slots <a href="https://casinoplay.icu/">parx casino online</a> casinos near me <a href="https://casinoplay.icu/">foxwoods casino online slots</a> | https://casinoplay.icu/ - cherokee casino https://casinoplay.icu/ - hollywood casino play4fun

hollywood casino tcump

ilani casino <a href="https://casinorealmoney.ooo/">parx online casino</a> penny slots <a href="https://casinorealmoney.ooo/">fortune bay casino</a> | https://casinorealmoney.ooo/ - mgm online casino nj https://casinorealmoney.ooo/ - casino games slots free

free casino games slot machines wiphj

casino blackjack <a href="https://onlinecasinoslotsmaxx.com/">lady luck</a> best online casino <a href="https://onlinecasinoslotsmaxx.com/">high 5 casino</a> | https://onlinecasinoslotsmaxx.com/ - free casino games slotomania https://onlinecasinoslotsmaxx.com/ - borgata online casino

pop slots cjinz

infinity slots <a href="https://freecasinoslots.icu/">my vegas slots</a> simslots free slots <a href="https://freecasinoslots.icu/">online slot games</a> | https://freecasinoslots.icu/ - house of fun slots https://freecasinoslots.icu/ - free slot play

best online casino unbug

play casino <a href="https://onlinecasinoplay24.us.org/">free online casino games</a> casino bonus <a href="https://onlinecasinoplay24.us.org/">borgata online casino</a> | https://onlinecasinoplay24.us.org/ - free slots games https://onlinecasinoplay24.us.org/ - casino slots

best online casino qicfn

mohegan sun online casino <a href="https://casinorealmoney.icu/">online casino</a> zone online casino games <a href="https://casinorealmoney.icu/">doubledown casino promo codes</a> | https://casinorealmoney.icu/ - blue chip casino https://casinorealmoney.icu/ - hollywood casino play4fun

slot games xjins

igt free slots <a href="https://vegasslotsonline.icu/">online slot games</a> best online slots <a href="https://vegasslotsonline.icu/">slots online free</a> | https://vegasslotsonline.icu/ - slots lounge https://vegasslotsonline.icu/ - hollywood casino free slots online

play free vegas casino games owzma

casino games slots free <a href="https://onlinecasino888.us.org/">free casino games</a> real casino <a href="https://onlinecasino888.us.org/">free casino games no download</a> | https://onlinecasino888.us.org/ - parx online casino https://onlinecasino888.us.org/ - free casino games slotomania

online slot games nbxxb

vegas slots online <a href="https://slotgames.icu/">caesars slots free casino</a> house of fun free slots <a href="https://slotgames.icu/">vegas slots online</a> | https://slotgames.icu/ - free slots no download https://slotgames.icu/ - slots of vegas casino

gossip slots tayoi

pompeii slots <a href="https://playslotsonline.icu/">online slots free</a> world class casino slots <a href="https://playslotsonline.icu/">free slots slotomania</a> | https://playslotsonline.icu/ - online casino slots https://playslotsonline.icu/ - gsn casino slots

virgin casino online ewnye

casino near me <a href="https://realmoneycasino.ooo/">hollywood casino</a> casino games free <a href="https://realmoneycasino.ooo/">parx online casino</a> | https://realmoneycasino.ooo/ - borgata online casino https://realmoneycasino.ooo/ - foxwoods casino

slot machines flutt

play casino <a href="https://slotsonline.ooo/">parx online casino</a> choctaw casino <a href="https://slotsonline.ooo/">play free vegas casino games</a> | https://slotsonline.ooo/ - hard rock casino atlantic city https://slotsonline.ooo/ - casino slots

doubleu casino eajeb

free casino slot games <a href="https://playslots.ooo/">spirit mountain casino</a> treasure island casino minnesota <a href="https://playslots.ooo/">downstream casino</a> | https://playslots.ooo/ - morongo casino https://playslots.ooo/ - rivers casino pittsburgh

free casino games slots odxxk

free slots games <a href="https://onlinecasinoplay.us.org/">vegas world casino games</a> firekeepers casino <a href="https://onlinecasinoplay.us.org/">free casino games sun moon</a> | https://onlinecasinoplay.us.org/ - play online casino https://onlinecasinoplay.us.org/ - online gambling

caesars online casino ajzec

viejas casino <a href="https://casinoo.ooo/">harrah cherokee casino</a> 888 casino <a href="https://casinoo.ooo/">online casino games free</a> | https://casinoo.ooo/ - clearwater casino https://casinoo.ooo/ - winstar casino

harrah online casino evbje

vegas casino games <a href="https://freeonlineslots.ooo/">double down casino</a> casino game <a href="https://freeonlineslots.ooo/">treasure island casino minnesota</a> | https://freeonlineslots.ooo/ - hollywood casino play4fun https://freeonlineslots.ooo/ - real casino

online casino gambling illsh

rock n cash casino slots <a href="https://vegascasino.icu/">sugarhouse casino online</a> ignition casino <a href="https://vegascasino.icu/">golden nugget online casino</a> | https://vegascasino.icu/ - muckleshoot casino https://vegascasino.icu/ - grand falls casino

gsn casino slots yjnkh

casino slots <a href="https://trstonlinecasino.com/">vegas world casino games</a> gsn casino <a href="https://trstonlinecasino.com/">casino online slots</a> | https://trstonlinecasino.com/ - free casino games slot machines https://trstonlinecasino.com/ - free casino games

doubledown casino promo codes mdkuu

san manuel online casino <a href="https://playonlinecasino.icu/">river spirit casino tulsa</a> gambling sites <a href="https://playonlinecasino.icu/">gsn casino</a> | https://playonlinecasino.icu/ - free casino games slot machines https://playonlinecasino.icu/ - free online casino slots

Pages

Add new comment

Target Image