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

best online casinos uhemy

caesars casino <a href="https://onlinecasinoplayslots.us.org/">jackpot party casino</a> foxwoods casino online slots <a href="https://onlinecasinoplayslots.us.org/">empire city casino online</a> | https://onlinecasinoplayslots.us.org/ - foxwoods casino https://onlinecasinoplayslots.us.org/ - casino online slots

slots of vegas qyexs

hearts of vegas free slots <a href="https://casino-slots.fun/">monopoly slots</a> my vegas slots <a href="https://casino-slots.fun/">slots free games</a> | https://casino-slots.fun/ - old vegas slots https://casino-slots.fun/ - hollywood casino online slots free

slot games bputt

slots games free <a href="https://myslotsbuddy.com/">slotomania on facebook</a> vegas free slots <a href="https://myslotsbuddy.com/">charlestown races and slots</a> | https://myslotsbuddy.com/ - heart of vegas free slots https://myslotsbuddy.com/ - hollywood casino free slot play

free online slots no download kgapy

vegas slots online <a href="https://slotsonline.tools/">slots farm</a> casino games slots free <a href="https://slotsonline.tools/">play slots online</a> | https://slotsonline.tools/ - free online slots vegas world https://slotsonline.tools/ - vegas slots online free

northern quest casino syrsw

online gambling sites <a href="https://online-casino.fun/">casino games slots free</a> soaring eagle casino <a href="https://online-casino.fun/">sugarhouse casino online</a> | https://online-casino.fun/ - foxwoods casino https://online-casino.fun/ - san manuel online casino

turning stone online slots aqmse

free slots online <a href="https://casinoslot.icu/">slots online</a> slots for free <a href="https://casinoslot.icu/">monopoly slots</a> | https://casinoslot.icu/ - cashman casino slots free https://casinoslot.icu/ - free slots online no download

slots for real money fovzs

high 5 casino <a href="https://onlinecasino.us.org/">casino online</a> casino games <a href=" https://onlinecasino.us.org/">casino real money</a> | https://onlinecasino.us.org/ - high 5 casino https://onlinecasino.us.org/ - chumba casino

house of fun slots xeekz

free slots vegas <a href="https://freeonlineslots.icu/">casino games slots free</a> cashman casino slots free <a href="https://freeonlineslots.icu/">slotomania free slots</a> | https://freeonlineslots.icu/ - free casino slots games https://freeonlineslots.icu/ - free slots machines

online slots free ohftz

gold fish casino slots <a href="https://vegascasino.tools/">casino slots free</a> mohegan sun free online slots <a href="https://vegascasino.tools/">free penny slots</a> | https://vegascasino.tools/ - best online slots https://vegascasino.tools/ - pompeii slots

world class casino slots nnqqo

slots lounge <a href="https://slotswall.com/">pompeii slots</a> free slots with no download or registration <a href="https://slotswall.com/">free penny slots no download</a> | https://slotswall.com/ - quick hits free slots https://slotswall.com/ - slotomania on facebook

free slot machine games xlvvn

best online slots <a href="https://playslotscasino.fun/">house of fun free slots</a> free online slots <a href="https://playslotscasino.fun/">free slots games</a> | https://playslotscasino.fun/ - free video slots https://playslotscasino.fun/ - vegas world free games online slots

soaring eagle casino xrbjy

plainridge casino <a href="https://casinoo.icu/">hard rock casino tampa</a> casinos in iowa <a href="https://casinoo.icu/">doubledown casino promo codes</a> | https://casinoo.icu/ - codeshareonline doubledown casino https://casinoo.icu/ - lady luck online casino

vegas slots free zmcph

buffalo gold slots <a href="https://playcasinoslots.fun/">gossip slots</a> slots lounge <a href="https://playcasinoslots.fun/">free slots</a> | https://playcasinoslots.fun/ - free vegas slots online casino https://playcasinoslots.fun/ - monopoly slots

free slots slotomania tnvqx

house of fun free slots <a href="https://playslots.icu/">vegas world free slots</a> real casino slots <a href="https://playslots.icu/">charlestown races and slots</a> | https://playslots.icu/ - penny slots free online https://playslots.icu/ - free slots machines

online casino slots njxue

gamepoint slots <a href="https://slotsgames.icu/">free vegas slots online</a> slots games <a href="https://slotsgames.icu/">free slot play no download</a> | https://slotsgames.icu/ - free slot games with no download https://slotsgames.icu/ - monopoly slots

mountaineer casino chewe

play online casino <a href="https://online-casinos.icu/">casino games slots free</a> river spirit casino tulsa <a href="https://online-casinos.icu/">northern quest casino</a> | https://online-casinos.icu/ - doubledown casino https://online-casinos.icu/ - doubledown casino facebook

tropicana online casino kdhyo

dakota sioux casino <a href="http://online-games.icu/">free online casino slots</a> real casino <a href="http://online-games.icu/">casinos near me</a> | http://online-games.icu/ - bigfish casino online games http://online-games.icu/ - belterra casino

cashman casino ksley

belterra casino <a href="https://onlinecasinoasti.com/">bovada blackjack</a> cashman casino <a href="https://onlinecasinoasti.com/">red rock casino</a> | https://onlinecasinoasti.com/ - rock n cash casino slots https://onlinecasinoasti.com/ - huuuge casino

free casino slots no download rnhnu

myvegas slots <a href="https://casinoslotsparty.com/">free slots 777</a> gamepoint slots <a href="https://casinoslotsparty.com/">hot shot casino slots</a> | https://casinoslotsparty.com/ - brian christopher slots https://casinoslotsparty.com/ - pop slots

zone online casino games vneav

online casino real money <a href="https://onlinecasino888.us.org/">sugarhouse online casino</a> prairie meadows casino <a href="https://onlinecasino888.us.org/">free vegas slots online casino</a> | https://onlinecasino888.us.org/ - casino games slots free https://onlinecasino888.us.org/ - double u casino

high five casino slots vigje

vegas world casino games <a href="https://onlinecasinoslotsmaxx.com/">oxford casino</a> potawatomi casino <a href="https://onlinecasinoslotsmaxx.com/">zone online casino games</a> | https://onlinecasinoslotsmaxx.com/ - vegas casino games https://onlinecasinoslotsmaxx.com/ - cherokee casino

real money casino zmxkb

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

free casino slot games xmqag

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

hallmark casino online qwnej

betfair casino online nj <a href="https://casinorealmoney.fun/">belterra casino</a> doubleu casino <a href="https://casinorealmoney.fun/">pala casino</a> | https://casinorealmoney.fun/ - thunder valley casino https://casinorealmoney.fun/ - hyper casinos

play slots online nyrwx

hollywood casino online slots free <a href="https://slotsonline.fun/">slots free</a> las vegas free slots <a href="https://slotsonline.fun/">slotomania free slots</a> | https://slotsonline.fun/ - online casino slots no download https://slotsonline.fun/ - free games online slots

free slots vegas world cxamp

free slots slotomania <a href="https://playslots.tools/">mirrorball slots</a> slots of vegas casino <a href="https://playslots.tools/">celebrity slots</a> | https://playslots.tools/ - gossip slots https://playslots.tools/ - gold fish casino slots

pch slots tournament rozbp

liberty slots <a href="http://freeslots.icu/">scatter slots</a> hot shot casino slots <a href="http://freeslots.icu/">play slots online</a> | http://freeslots.icu/ - free slot play http://freeslots.icu/ - vegas slots free

empire casino online awosr

chumash casino <a href="https://onlinecasinowp.us.org/">gambling sites</a> royal river casino <a href="https://onlinecasinowp.us.org/">sugarhouse casino online</a> | https://onlinecasinowp.us.org/ - rock n cash casino slots https://onlinecasinowp.us.org/ - casino slots

gossip slots xleev

pop slots casino <a href="https://playslots.icu/">buffalo slots</a> cashman casino slots <a href="https://playslots.icu/">buffalo gold slots</a> | https://playslots.icu/ - free slots games https://playslots.icu/ - slots games free

free online slots etnid

caesars slots <a href="https://onlineslotgames.tools/">gold fish casino slots</a> hollywood slots <a href="https://onlineslotgames.tools/">caesars free slots</a> | https://onlineslotgames.tools/ - casino slots https://onlineslotgames.tools/ - konami free slots

chumba casino qghaz

hollywood online casino <a href="https://casino-games.icu/">turning stone casino</a> harrah cherokee casino <a href="https://casino-games.icu/">twin river casino</a> | https://casino-games.icu/ - gsn casino https://casino-games.icu/ - online slot machines

liberty slots qrjhr

slots of vegas <a href="https://online-slots.fun/">casino slots free games</a> vegas slots online free <a href="https://online-slots.fun/">celebrity slots</a> | https://online-slots.fun/ - cashman casino slots https://online-slots.fun/ - free online slots games

casino slot wgxgr

mountaineer casino <a href="https://onlinecasinogames.fun/">pala casino online</a> grand falls casino <a href="https://onlinecasinogames.fun/">chumba casino</a> | https://onlinecasinogames.fun/ - greektown casino https://onlinecasinogames.fun/ - free casino games

pechanga casino ykfpp

slot machines <a href="http://online-casino.icu/">sandia casino</a> free vegas slots online casino <a href="http://online-casino.icu/">cherokee casino</a> | http://online-casino.icu/ - casino bonus http://online-casino.icu/ - bovada casino

free video slots icotr

free casino slots <a href="https://slotsgamesgiant.com/">free slots slotomania</a> infinity slots <a href="https://slotsgamesgiant.com/">hollywood casino free slots online</a> | https://slotsgamesgiant.com/ - free online casino slots https://slotsgamesgiant.com/ - best online slots

goldfish casino slots free ooptw

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

mgm online casino nj udhmh

slotomania slot machines <a href="https://onlinecasinoplayslots.us.org/">casino games</a> rivers casino schenectady <a href="https://onlinecasinoplayslots.us.org/">virgin online casino</a> | https://onlinecasinoplayslots.us.org/ - doubledown casino promo codes https://onlinecasinoplayslots.us.org/ - casino slots

hearts of vegas free slots zhzqh

pch slots tournament <a href="https://playcasinoslots.icu/">my vegas slots</a> gsn casino slots <a href="https://playcasinoslots.icu/">slots of vegas casino</a> | https://playcasinoslots.icu/ - free online slots no download https://playcasinoslots.icu/ - free slots with no download or registration

mountaineer casino mfwda

gsn casino on facebook <a href="https://usaonlinecasinogames.us.org/">oxford casino</a> play online casino <a href="https://usaonlinecasinogames.us.org/">caesars casino</a> | https://usaonlinecasinogames.us.org/ - jackpot party casino facebook https://usaonlinecasinogames.us.org/ - doubleu casino

plainridge casino ggwxu

huuuge casino <a href="https://onlinecasinoplay24.us.org/">caesars casino online</a> vegas casino games <a href="https://onlinecasinoplay24.us.org/">red rock casino</a> | https://onlinecasinoplay24.us.org/ - empire city casino https://onlinecasinoplay24.us.org/ - san manuel casino

sugarhouse online casino lfrmu

empire city casino online <a href="https://onlinecasinogamesy.us.org/">mgm online casino</a> royal river casino <a href="https://onlinecasinogamesy.us.org/">jackpot party casino</a> | https://onlinecasinogamesy.us.org/ - bovada blackjack https://onlinecasinogamesy.us.org/ - hallmark casino online

meadows casino flwvb

slots for real money <a href="https://casinoplay.fun/">ilani casino</a> high five casino slots <a href="https://casinoplay.fun/">empire city casino</a> | https://casinoplay.fun/ - foxwoods casino online https://casinoplay.fun/ - hollywood online casino

casino online slots jlrwo

pala casino online <a href="https://online-casino-games.fun/">big fish casino</a> mountaineer casino <a href="https://online-casino-games.fun/">grand falls casino</a> | https://online-casino-games.fun/ - foxwoods casino online slots https://online-casino-games.fun/ - hampton beach casino ballroom

cashman casino slots jdtzt

liberty slots casino <a href="https://myslotsbuddy.com/">hot shot casino slots</a> hollywood casino online slots <a href="https://myslotsbuddy.com/">monopoly slots</a> | https://myslotsbuddy.com/ - online slots https://myslotsbuddy.com/ - online casino slots

caesars free slots online yfbjh

slot machines <a href="https://casino-slots.fun/">slot games free</a> mohegan sun free online slots <a href="https://casino-slots.fun/">play free slots for fun</a> | https://casino-slots.fun/ - pch slots tournament https://casino-slots.fun/ - caesars slots free casino

doubledown casino free slots dcbdw

charlestown races and slots <a href="https://casinoslots.tools/">free penny slots online</a> jackpot party casino slots <a href="https://casinoslots.tools/">my vegas slots</a> | https://casinoslots.tools/ - slotomania free slots https://casinoslots.tools/ - hollywood casino free slots online

freeslots.com slots rmdsm

caesars free slots <a href="https://slotswall.com/">caesars slots</a> online slots <a href="https://slotswall.com/">free slots games online</a> | https://slotswall.com/ - free casino slots no download https://slotswall.com/ - freeslots.com slots

free slots with no download or registration dbrhw

liberty slots <a href="https://slotsonline.tools/">wheel of fortune slots</a> play free slots <a href="https://slotsonline.tools/">free online slots</a> | https://slotsonline.tools/ - quick hits free slots https://slotsonline.tools/ - free slots 777

free online slots games aeeki

quick hits free slots <a href="https://freeonlineslots.icu/">liberty slots casino</a> world class casino slots <a href="https://freeonlineslots.icu/">free games for casino slots hollywood</a> | https://freeonlineslots.icu/ - pompeii slots https://freeonlineslots.icu/ - free slots games online

online gambling nuppm

gun lake casino <a href="https://onlinecasinoasti.com/">three rivers casino</a> hollywood casino online <a href="https://onlinecasinoasti.com/">grand falls casino</a> | https://onlinecasinoasti.com/ - betfair casino online nj https://onlinecasinoasti.com/ - seneca niagara casino

Pages

Add new comment

Target Image