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 slots 777 yalwd

free slots no download no registration <a href="https://playslotscasino.fun/">da vinci diamonds free online slots</a> charlestown races and slots <a href="https://playslotscasino.fun/">free online slots games</a> | https://playslotscasino.fun/ - real casino slots https://playslotscasino.fun/ - liberty slots

hot shot casino slots dwbos

slots games free <a href="https://vegascasino.tools/">turning stone online slots</a> world class casino slots <a href="https://vegascasino.tools/">myvegas slots</a> | https://vegascasino.tools/ - scatter slots https://vegascasino.tools/ - cashman casino slots

slotomania on facebook bvuys

slots free <a href="https://casinoslot.icu/">free slot machine games</a> free penny slots online <a href="https://casinoslot.icu/">house of fun free slots</a> | https://casinoslot.icu/ - house of fun free slots https://casinoslot.icu/ - penny slots free

play casino xyijw

casino blackjack <a href="https://onlinecasino.us.org/">doubledown casino</a> zone online casino <a href=" https://onlinecasino.us.org/">real money casino</a> | https://onlinecasino.us.org/ - parx online casino https://onlinecasino.us.org/ - chumba casino

riverwind casino zmgyr

snoqualmie casino <a href="https://online-casino.fun/">bovada casino</a> grand falls casino <a href="https://online-casino.fun/">casino games</a> | https://online-casino.fun/ - greektown casino https://online-casino.fun/ - free online casino

online casinos kklnr

online casino real money <a href="https://playonlinecasino.us.org/">gsn casino slots</a> casino games | https://playonlinecasino.us.org/ - bovada casino

pala casino diwbi

empire city casino online <a href="https://casinoo.icu/">online gambling sites</a> vegas casino games <a href="https://casinoo.icu/">online casino gambling</a> | https://casinoo.icu/ - tropicana online casino https://casinoo.icu/ - free casino

monopoly slots gihlc

goldfish casino slots free <a href="https://casinoslotsparty.com/">free slots machines</a> free video slots <a href="https://casinoslotsparty.com/">hollywood casino free slot play</a> | https://casinoslotsparty.com/ - free penny slots no download https://casinoslotsparty.com/ - slots free

gamepoint slots oqske

online slots free <a href="https://playcasinoslots.fun/">house of fun slots</a> free online slots vegas world <a href="https://playcasinoslots.fun/">vegas slots online</a> | https://playcasinoslots.fun/ - cleopatra slots https://playcasinoslots.fun/ - jackpot party casino slots

doubledown casino free slots epphj

wizard of oz slots <a href="https://slotsgames.icu/">hollywood casino online slots</a> online slot games <a href="https://slotsgames.icu/">turning stone online slots</a> | https://slotsgames.icu/ - free games online slots https://slotsgames.icu/ - ruby slots

caesars slots free casino udvir

hollywood casino free slots <a href="https://playslots.icu/">hollywood casino free slots online</a> vegas free slots online <a href="https://playslots.icu/">free slot play no download</a> | https://playslots.icu/ - best online slots https://playslots.icu/ - slots farm

clearwater casino rfwms

double down casino <a href="https://online-casinos.icu/">parx online casino</a> casino slot <a href="https://online-casinos.icu/">meadows casino</a> | https://online-casinos.icu/ - big fish casino slots https://online-casinos.icu/ - sugarhouse casino online

online casino gambling iewrn

big fish casino slots <a href="http://online-games.icu/">high 5 casino</a> pala casino online <a href="http://online-games.icu/">lincoln casino</a> | http://online-games.icu/ - fortune bay casino http://online-games.icu/ - best online casinos

free casino games fscyb

northern quest casino <a href="https://onlinecasino888.us.org/">sugarhouse casino online</a> online casino reviews <a href="https://onlinecasino888.us.org/">cherokee casino</a> | https://onlinecasino888.us.org/ - hard rock casino tampa https://onlinecasino888.us.org/ - three rivers casino

caesar casino online slot games kqcyb

free casino <a href="https://onlinecasinoslotsmaxx.com/">winstar casino oklahoma</a> double u casino <a href="https://onlinecasinoslotsmaxx.com/">slots casino games</a> | https://onlinecasinoslotsmaxx.com/ - golden nugget online casino https://onlinecasinoslotsmaxx.com/ - real casino slots

free online slots no download uflcx

slotomania free slots <a href="https://playslots.tools/">free games online slots</a> free slots slotomania <a href="https://playslots.tools/">penny slots free</a> | https://playslots.tools/ - my vegas slots https://playslots.tools/ - quick hit slots

real casino slots zdrsc

mohegan sun free online slots <a href="https://onlineslots.tools/">penny slots free online</a> free vegas slots <a href="https://onlineslots.tools/">casino slots free games</a> | https://onlineslots.tools/ - world class casino slots https://onlineslots.tools/ - pop slots casino

free video slots nvpsu

slots of vegas casino <a href="http://freeslots.icu/">slots games free</a> free slots vegas world <a href="http://freeslots.icu/">gossip slots</a> | http://freeslots.icu/ - free slots 777 http://freeslots.icu/ - penny slots free

online casinos for us players ucbap

empire city casino online <a href="https://casinorealmoney.fun/">casino slot</a> treasure island casino minnesota <a href="https://casinorealmoney.fun/">doubledown casino</a> | https://casinorealmoney.fun/ - island view casino https://casinorealmoney.fun/ - casino online slots

free slot play vyarb

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

slot machines pndaz

free vegas slots <a href="https://playslots.icu/">wheel of fortune slots</a> hollywood slots <a href="https://playslots.icu/">pompeii slots</a> | https://playslots.icu/ - hollywood casino free slots https://playslots.icu/ - free slots vegas

big fish casino aujcj

mgm online casino nj <a href="https://onlinecasinowp.us.org/">vegas casino games</a> cafe casino online <a href="https://onlinecasinowp.us.org/">greektown casino</a> | https://onlinecasinowp.us.org/ - mountaineer casino https://onlinecasinowp.us.org/ - gsn casino on facebook

free online slots no download xtrwi

hollywood casino free slots <a href="https://slotsgamesgiant.com/">cashman casino slots</a> free slots games online <a href="https://slotsgamesgiant.com/">monopoly slots</a> | https://slotsgamesgiant.com/ - free online slots no download no registration https://slotsgamesgiant.com/ - pch slots tournament

free vegas world slots gklpn

gossip slots <a href="https://slotsgameshub.com/">free slots with no download or registration</a> free online casino slots <a href="https://slotsgameshub.com/">slots of vegas</a> | https://slotsgameshub.com/ - hot shot casino slots https://slotsgameshub.com/ - free online slots no download no registration

online slots dufnp

mohegan sun free online slots <a href="https://onlineslotgames.tools/">slots free online</a> online casino slots <a href="https://onlineslotgames.tools/">buffalo slots</a> | https://onlineslotgames.tools/ - best online slots https://onlineslotgames.tools/ - free slots vegas

turning stone online casino julxa

blue chip casino <a href="https://onlinecasinogames.fun/">casino online</a> twin river casino <a href="https://onlinecasinogames.fun/">doubleu casino on facebook</a> | https://onlinecasinogames.fun/ - lincoln casino https://onlinecasinogames.fun/ - caesar casino online slot games

fortune bay casino ecsvd

jackpot party casino <a href="https://onlinecasinoplayslots.us.org/">online casino</a> mohegan sun casino <a href="https://onlinecasinoplayslots.us.org/">high 5 casino</a> | https://onlinecasinoplayslots.us.org/ - rock n cash casino slots https://onlinecasinoplayslots.us.org/ - jackpot party casino

casino play iqupn

online casino gambling <a href="https://casino-games.icu/">seneca allegany casino</a> usa online casino <a href="https://casino-games.icu/">sandia casino</a> | https://casino-games.icu/ - casino blackjack https://casino-games.icu/ - online casino real money

gsn casino slots qijrk

free slots 777 <a href="https://online-slots.fun/">hearts of vegas free slots</a> free online slots games <a href="https://online-slots.fun/">free slots vegas world</a> | https://online-slots.fun/ - mirrorball slots https://online-slots.fun/ - caesars free slots

myvegas slots rjrma

casino slots <a href="https://slotswall.com/">free casino slots</a> cashman casino slots <a href="https://slotswall.com/">buffalo gold slots</a> | https://slotswall.com/ - gossip slots https://slotswall.com/ - monopoly slots

tropicana online casino ruekd

hallmark casino online <a href="http://online-casino.icu/">casinos in iowa</a> foxwoods casino online <a href="http://online-casino.icu/">treasure island casino minnesota</a> | http://online-casino.icu/ - hollywood casino http://online-casino.icu/ - plainridge casino

best online slots akxqr

slots for free <a href="https://myslotsbuddy.com/">world class casino slots</a> free slots online <a href="https://myslotsbuddy.com/">slots free online</a> | https://myslotsbuddy.com/ - turning stone online slots https://myslotsbuddy.com/ - online slots

gambling sites tzxvn

chumba casino <a href="https://onlinecasinoasti.com/">oxford casino</a> ilani casino <a href="https://onlinecasinoasti.com/">seneca niagara casino</a> | https://onlinecasinoasti.com/ - casino slots https://onlinecasinoasti.com/ - harrah online casino

buffalo gold slots vnsgd

quick hit slots <a href="https://playcasinoslots.icu/">vegas casino slots</a> slots online <a href="https://playcasinoslots.icu/">slots games free</a> | https://playcasinoslots.icu/ - brian christopher slots https://playcasinoslots.icu/ - heart of vegas free slots

casino games jieeu

slots for real money <a href="https://usaonlinecasinogames.us.org/">casinos in iowa</a> virgin online casino <a href="https://usaonlinecasinogames.us.org/">turning stone casino</a> | https://usaonlinecasinogames.us.org/ - online gambling https://usaonlinecasinogames.us.org/ - mgm online casino

online casino bonus wpjjo

sugarhouse online casino <a href="https://casinoplay.fun/">virgin online casino</a> jackpot party casino facebook <a href="https://casinoplay.fun/">virgin casino online</a> | https://casinoplay.fun/ - mohegan sun online casino https://casinoplay.fun/ - downstream casino

casino games vwidk

double down casino <a href="https://onlinecasinoplay24.us.org/">empire city casino</a> fortune bay casino <a href="https://onlinecasinoplay24.us.org/">high five casino slots</a> | https://onlinecasinoplay24.us.org/ - playmgm nj casino online https://onlinecasinoplay24.us.org/ - free online casino slots

slots casino games ntjho

bigfish casino online games <a href="https://onlinecasinogamesy.us.org/">foxwoods casino</a> mohegan sun online casino <a href="https://onlinecasinogamesy.us.org/">pala casino online</a> | https://onlinecasinogamesy.us.org/ - mohegan sun casino https://onlinecasinogamesy.us.org/ - island view casino

high 5 casino tnqjk

casino online slots <a href="https://playonlinecasino.us.org/">bovada casino</a> play online casino | https://playonlinecasino.us.org/ - casino online

free online slots no download xohfn

da vinci diamonds free online slots <a href="https://casino-slots.fun/">heart of vegas slots</a> slots online <a href="https://casino-slots.fun/">hot shot casino slots</a> | https://casino-slots.fun/ - buffalo slots https://casino-slots.fun/ - free vegas slots

free slots games online oxixq

house of fun free slots <a href="https://slotsonline.tools/">penny slots</a> caesars slots free casino <a href="https://slotsonline.tools/">mirrorball slots</a> | https://slotsonline.tools/ - free slots casino games https://slotsonline.tools/ - free video slots

charlestown races and slots wepgf

vegas casino slots <a href="https://casinoslots.tools/">slot games free</a> goldfish casino slots free <a href="https://casinoslots.tools/">brian christopher slots</a> | https://casinoslots.tools/ - slot machine games free https://casinoslots.tools/ - penny slots

casino online tontj

play casino <a href="https://online-casino-games.fun/">hollywood online casino</a> free online casino games <a href="https://online-casino-games.fun/">zone online casino</a> | https://online-casino-games.fun/ - huuuge casino https://online-casino-games.fun/ - chumash casino

free casino slots no download kihzm

world class casino slots <a href="https://playslotscasino.fun/">free penny slots</a> play free slots for fun <a href="https://playslotscasino.fun/">old vegas slots</a> | https://playslotscasino.fun/ - old vegas slots https://playslotscasino.fun/ - free slots slotomania

vegas slots edtsm

igt free slots <a href="https://freeonlineslots.icu/">hollywood slots</a> caesars free slots <a href="https://freeonlineslots.icu/">casino slots</a> | https://freeonlineslots.icu/ - free slot games with no download https://freeonlineslots.icu/ - hollywood casino free slots online

empire city online casino kpxfa

casino real money <a href="https://onlinecasino.us.org/">gsn casino slots</a> casino bonus <a href=" https://onlinecasino.us.org/">online gambling casino</a> | https://onlinecasino.us.org/ - casino play https://onlinecasino.us.org/ - bovada casino

cashman casino slots free rluzu

free slot games with no download <a href="https://casinoslotsparty.com/">vegas slots online free</a> free slots vegas <a href="https://casinoslotsparty.com/">goldfish casino slots free</a> | https://casinoslotsparty.com/ - caesars free slots online https://casinoslotsparty.com/ - vegas free slots online

las vegas free slots gvaum

free slot play no download <a href="https://casinoslot.icu/">free casino slots</a> free slot games online <a href="https://casinoslot.icu/">celebrity slots</a> | https://casinoslot.icu/ - free casino slots games https://casinoslot.icu/ - vegas slots online

sugarhouse casino wahef

foxwoods casino online <a href="https://casinoo.icu/">seneca niagara casino</a> caesars online casino <a href="https://casinoo.icu/">online casinos</a> | https://casinoo.icu/ - online casino gambling https://casinoo.icu/ - online gambling sites

playmgm nj casino online pxtyh

caesars casino online <a href="https://online-casino.fun/">meadows casino</a> casino online slots <a href="https://online-casino.fun/">turning stone casino</a> | https://online-casino.fun/ - caesars casino https://online-casino.fun/ - cashman casino

Pages

Add new comment

Target Image