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

royal river casino rqdeg

meadows casino <a href="https://online-casino-games.fun/">online casino</a> hollywood online casino <a href="https://online-casino-games.fun/">harrah online casino</a> | https://online-casino-games.fun/ - online gambling sites https://online-casino-games.fun/ - jackpot party casino facebook

viejas casino muenu

rivers casino pittsburgh <a href="https://onlinecasinogames.fun/">slots casino games</a> casino play <a href="https://onlinecasinogames.fun/">posh casino</a> | https://onlinecasinogames.fun/ - red rock casino https://onlinecasinogames.fun/ - plainridge casino

golden nugget online casino umzks

slot machines <a href="https://onlinecasinoplay24.us.org/">casino play</a> riverwind casino <a href="https://onlinecasinoplay24.us.org/">parx casino online</a> | https://onlinecasinoplay24.us.org/ - del lago casino https://onlinecasinoplay24.us.org/ - 888 casino

vegas world free games online slots xlgrm

buffalo slots <a href="https://playslots.tools/">pch slots</a> free slots machines <a href="https://playslots.tools/">play free slots</a> | https://playslots.tools/ - buffalo slots https://playslots.tools/ - igt free slots

liberty slots casino pefqc

pop slots casino <a href="https://casinoslots.tools/">free slots games</a> free slots slotomania <a href="https://casinoslots.tools/">slots of vegas</a> | https://casinoslots.tools/ - free casino slot games https://casinoslots.tools/ - online slot games

plainridge casino cyork

casino slots <a href="https://online-casino.fun/">paradise casino</a> spirit mountain casino <a href="https://online-casino.fun/">chumba casino</a> | https://online-casino.fun/ - hollywood casino online https://online-casino.fun/ - san manuel online casino

rock n cash casino slots iivrj

casinos in iowa <a href="https://onlinecasinogamesy.us.org/">hollywood casino online slots</a> casino real money <a href="https://onlinecasinogamesy.us.org/">foxwoods casino online</a> | https://onlinecasinogamesy.us.org/ - winstar world casino https://onlinecasinogamesy.us.org/ - chinook winds casino

best online casinos kdyxe

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

hollywood casino free slots snoii

free slot machine games <a href="https://casinoslot.icu/">free slots games online</a> free online casino slots <a href="https://casinoslot.icu/">free slots 777</a> | https://casinoslot.icu/ - play free slots for fun https://casinoslot.icu/ - vegas slots online free

lady luck online casino toeze

del lago casino <a href="https://casinoplay.fun/">gsn casino slots</a> free vegas slots online casino <a href="https://casinoplay.fun/">online slot machines</a> | https://casinoplay.fun/ - free online casino https://casinoplay.fun/ - vegas world casino games

goldfish slots jqpze

vegas world free games online slots <a href="https://playcasinoslots.icu/">celebrity slots</a> free online slots no download no registration <a href="https://playcasinoslots.icu/">jackpot magic slots</a> | https://playcasinoslots.icu/ - best online slots https://playcasinoslots.icu/ - quick hit slots

mountaineer casino nozbs

big fish casino slots <a href="https://usaonlinecasinogames.us.org/">lady luck online casino</a> online gambling <a href="https://usaonlinecasinogames.us.org/">caesars casino online</a> | https://usaonlinecasinogames.us.org/ - casinos near me https://usaonlinecasinogames.us.org/ - cherokee casino

play slots ykrkq

free online slots no download <a href="https://casino-slots.fun/">brian christopher slots</a> free games online slots <a href="https://casino-slots.fun/">free slots no download</a> | https://casino-slots.fun/ - simslots free slots https://casino-slots.fun/ - jackpot magic slots

free buffalo slots eohqj

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

online casino gambling yjltf

parx online casino <a href="https://onlinecasino.us.org/">play online casino</a> bovada casino <a href=" https://onlinecasino.us.org/">casino play</a> | https://onlinecasino.us.org/ - virgin online casino https://onlinecasino.us.org/ - virgin online casino

online casino gambling kbzzy

soaring eagle casino <a href="https://onlinecasinoasti.com/">real money casino</a> pechanga casino <a href="https://onlinecasinoasti.com/">gsn casino slots</a> | https://onlinecasinoasti.com/ - winstar casino https://onlinecasinoasti.com/ - fortune bay casino

online casino slots jqfho

charlestown races and slots <a href="https://slotsgameshub.com/">hollywood casino free slots online</a> hollywood slots <a href="https://slotsgameshub.com/">vegas slots online free</a> | https://slotsgameshub.com/ - real casino slots https://slotsgameshub.com/ - slots games

hollywood casino free slots online mrtgy

brian christopher slots <a href="https://slotsgamesgiant.com/">hollywood casino online slots free</a> vegas free slots online <a href="https://slotsgamesgiant.com/">gold fish casino slots</a> | https://slotsgamesgiant.com/ - free slots no download https://slotsgamesgiant.com/ - free slots with no download or registration

seneca niagara casino uerez

online casino reviews <a href="https://casinoo.icu/">play online casino</a> mgm online casino nj <a href="https://casinoo.icu/">high five casino slots</a> | https://casinoo.icu/ - online casino slots https://casinoo.icu/ - jack online casino

golden nugget online casino wklxb

empire city casino <a href="https://onlinecasinoplayslots.us.org/">sandia casino</a> meadows casino <a href="https://onlinecasinoplayslots.us.org/">grand casino hinckley</a> | https://onlinecasinoplayslots.us.org/ - vegas casino games https://onlinecasinoplayslots.us.org/ - mystic lake casino

charlestown races and slots kuqft

liberty slots <a href="https://freeonlineslots.icu/">hot shot casino slots</a> mohegan sun free online slots <a href="https://freeonlineslots.icu/">free slot games with no download</a> | https://freeonlineslots.icu/ - slot machine games free https://freeonlineslots.icu/ - myvegas slots

goldfish casino slots free dacgz

free slot games <a href="https://vegascasino.tools/">penny slots free online</a> slots for free <a href="https://vegascasino.tools/">las vegas free slots</a> | https://vegascasino.tools/ - free games for casino slots hollywood https://vegascasino.tools/ - penny slots

slotomania on facebook oruuh

free slots vegas <a href="https://playcasinoslots.fun/">doubledown casino free slots</a> free vegas world slots <a href="https://playcasinoslots.fun/">liberty slots casino</a> | https://playcasinoslots.fun/ - real casino slots https://playcasinoslots.fun/ - free slots with no download or registration

free online slots no download no registration nhzwy

free games online slots <a href="https://myslotsbuddy.com/">free slot play</a> hollywood casino free slots <a href="https://myslotsbuddy.com/">vegas world slots</a> | https://myslotsbuddy.com/ - online slots free https://myslotsbuddy.com/ - wheel of fortune slots

free slot play no download hjzux

celebrity slots <a href="https://slotsgames.icu/">hollywood casino free slot play</a> slot games free <a href="https://slotsgames.icu/">free buffalo slots</a> | https://slotsgames.icu/ - slots farm https://slotsgames.icu/ - old vegas slots

hollywood casino atdzh

choctaw casino <a href="http://online-games.icu/">jackpot party casino</a> slots casino games <a href="http://online-games.icu/">free online casino games</a> | http://online-games.icu/ - online casino games http://online-games.icu/ - casino games slots free

play slots jymqj

buffalo gold slots <a href="https://playslotscasino.fun/">jackpot magic slots</a> hollywood slots <a href="https://playslotscasino.fun/">cashman casino slots</a> | https://playslotscasino.fun/ - free slots slotomania https://playslotscasino.fun/ - gossip slots

casino play peybv

cashman casino <a href="https://onlinecasinoslotsmaxx.com/">casinos in iowa</a> foxwoods casino <a href="https://onlinecasinoslotsmaxx.com/">online casino real money</a> | https://onlinecasinoslotsmaxx.com/ - choctaw casino https://onlinecasinoslotsmaxx.com/ - dakota sioux casino

pch slots fphoo

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

cashman casino sjudq

winstar world casino <a href="https://online-casinos.icu/">gambling sites</a> betfair casino online nj <a href="https://online-casinos.icu/">jack online casino</a> | https://online-casinos.icu/ - gun lake casino https://online-casinos.icu/ - codeshareonline doubledown casino

online casino reviews uqhgj

bovada blackjack <a href="https://onlinecasino888.us.org/">casinos near me</a> online casino games <a href="https://onlinecasino888.us.org/">casino slot</a> | https://onlinecasino888.us.org/ - real casino https://onlinecasino888.us.org/ - dakota sioux casino

free slots hollywood xmxuy

vegas slots free <a href="https://slotsonline.fun/">konami free slots</a> gamepoint slots <a href="https://slotsonline.fun/">free buffalo slots</a> | https://slotsonline.fun/ - free games online slots https://slotsonline.fun/ - free slot games online

foxwoods online casino hufno

winstar world casino <a href="https://playonlinecasino.us.org/">best online casino</a> foxwoods online casino | https://playonlinecasino.us.org/ - online casino real money

foxwoods casino tqgkp

blue chip casino <a href="https://casinorealmoney.fun/">rivers casino pittsburgh</a> casino bonus <a href="https://casinorealmoney.fun/">soaring eagle casino</a> | https://casinorealmoney.fun/ - penny slots https://casinorealmoney.fun/ - ilani casino

firekeepers casino iazef

play online casino <a href="http://online-casino.icu/">hard rock casino atlantic city</a> foxwoods online casino <a href="http://online-casino.icu/">real money casino</a> | http://online-casino.icu/ - casino online slots http://online-casino.icu/ - ignition casino

online gambling casino mkaxz

codeshareonline doubledown casino <a href="https://casino-games.icu/">choctaw casino durant oklahoma</a> oxford casino <a href="https://casino-games.icu/">real money casino</a> | https://casino-games.icu/ - playmgm nj casino online https://casino-games.icu/ - potawatomi casino

brian christopher slots qkbdy

turning stone online slots <a href="https://slotswall.com/">myvegas slots</a> free video slots <a href="https://slotswall.com/">slots free games</a> | https://slotswall.com/ - slot machines https://slotswall.com/ - ruby slots

free vegas world slots lkmjd

ruby slots <a href="https://onlineslots.tools/">free slots no download no registration</a> doubledown casino free slots <a href="https://onlineslots.tools/">quick hits free slots</a> | https://onlineslots.tools/ - charlestown races and slots https://onlineslots.tools/ - gsn casino slots

harrah online casino fstuz

betfair casino online nj <a href="https://onlinecasinowp.us.org/">parx casino online</a> stn play online casino <a href="https://onlinecasinowp.us.org/">harrah cherokee casino</a> | https://onlinecasinowp.us.org/ - firekeepers casino https://onlinecasinowp.us.org/ - choctaw casino durant oklahoma

free slots games bgjlh

pch slots tournament <a href="https://onlineslotgames.tools/">slots games free</a> free slots online <a href="https://onlineslotgames.tools/">caesars slots free casino</a> | https://onlineslotgames.tools/ - hollywood slots https://onlineslotgames.tools/ - freeslots.com slots

hollywood casino free slot play mnulx

monopoly slots <a href="http://freeslots.icu/">online casino slots</a> house of fun slots <a href="http://freeslots.icu/">da vinci diamonds free online slots</a> | http://freeslots.icu/ - free casino slots http://freeslots.icu/ - pch slots

play slots online fxsyf

slots games <a href="https://casinoslotsparty.com/">scatter slots</a> free video slots <a href="https://casinoslotsparty.com/">casino slots free games</a> | https://casinoslotsparty.com/ - slotomania on facebook https://casinoslotsparty.com/ - play slots

vegas world free slots games ncrzj

free online slots no download <a href="https://online-slots.fun/">caesars slots free casino</a> cashman casino slots <a href="https://online-slots.fun/">pch slots tournament</a> | https://online-slots.fun/ - slot machines https://online-slots.fun/ - buffalo gold slots

free online slots no download no registration jhknd

best online slots <a href="https://playslots.icu/">free slots machines</a> vegas casino slots <a href="https://playslots.icu/">online casino slots no download</a> | https://playslots.icu/ - free vegas slots online casino https://playslots.icu/ - casino games slots free

casino slots free qdmbr

hot shot casino slots <a href="https://playslots.tools/">las vegas free slots</a> vegas slots <a href="https://playslots.tools/">scatter slots</a> | https://playslots.tools/ - liberty slots casino https://playslots.tools/ - wizard of oz slots

casino near me ffazj

posh casino <a href="https://onlinecasinogames.fun/">island view casino</a> parx online casino <a href="https://onlinecasinogames.fun/">twin river casino</a> | https://onlinecasinogames.fun/ - doubledown casino promo codes https://onlinecasinogames.fun/ - real money casino

slots for real money julbg

pala casino online <a href="https://online-casino-games.fun/">empire city casino online</a> free online casino slots <a href="https://online-casino-games.fun/">vegas casino games</a> | https://online-casino-games.fun/ - best online casino https://online-casino-games.fun/ - red rock casino

casino play wbdgl

mgm online casino nj <a href="https://onlinecasinoplay24.us.org/">double down casino</a> clearwater casino <a href="https://onlinecasinoplay24.us.org/">online slot machines</a> | https://onlinecasinoplay24.us.org/ - usa online casino https://onlinecasinoplay24.us.org/ - online slot machines

slots of vegas casino dkecw

liberty slots casino <a href="https://casinoslots.tools/">slot machine games free</a> infinity slots <a href="https://casinoslots.tools/">slots of vegas</a> | https://casinoslots.tools/ - play free slots for fun https://casinoslots.tools/ - caesars free slots

casino blackjack euzln

hallmark casino online <a href="https://onlinecasinoasti.com/">online casino slots</a> online casino bonus <a href="https://onlinecasinoasti.com/">casino play</a> | https://onlinecasinoasti.com/ - hollywood casino online https://onlinecasinoasti.com/ - zone online casino games

Pages

Add new comment

Target Image