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

real casino slots fueme

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

play slots online pnzfa

free slots games <a href="https://casinoslotsparty.com/">casino slots free</a> monopoly slots <a href="https://casinoslotsparty.com/">free casino slots</a> | https://casinoslotsparty.com/ - cashman casino slots free https://casinoslotsparty.com/ - cashman casino slots

free slot games with no download cntdx

brian christopher slots <a href="https://vegascasino.tools/">vegas free slots online</a> hollywood casino free slots <a href="https://vegascasino.tools/">vegas world free slots</a> | https://vegascasino.tools/ - cashman casino slots free https://vegascasino.tools/ - casino slots free games

double u casino lzshf

vegas world casino games <a href="http://online-games.icu/">slots for real money</a> borgata online casino <a href="http://online-games.icu/">free casino</a> | http://online-games.icu/ - jackpot party casino http://online-games.icu/ - rivers casino schenectady

doubledown casino ldwes

sandia casino <a href="https://casino-games.icu/">gun lake casino</a> mystic lake casino <a href="https://casino-games.icu/">online casino slots</a> | https://casino-games.icu/ - grand casino hinckley https://casino-games.icu/ - zone online casino

del lago casino qcjpn

treasure island casino minnesota <a href="https://onlinecasinoslotsmaxx.com/">casino online</a> free online casino slots <a href="https://onlinecasinoslotsmaxx.com/">gun lake casino</a> | https://onlinecasinoslotsmaxx.com/ - dakota sioux casino https://onlinecasinoslotsmaxx.com/ - double u casino

mgm online casino nj wmlip

downstream casino <a href="https://onlinecasinowp.us.org/">high 5 casino</a> vegas casino games <a href="https://onlinecasinowp.us.org/">doubleu casino</a> | https://onlinecasinowp.us.org/ - casinos near me https://onlinecasinowp.us.org/ - rock n cash casino slots

gsn casino slots ghsdk

casino slot <a href="https://online-casino.fun/">pala casino online nj</a> posh casino <a href="https://online-casino.fun/">caesars casino</a> | https://online-casino.fun/ - thunder valley casino https://online-casino.fun/ - northern quest casino

house of fun slots bzgil

online slots real money <a href="https://onlineslotgames.tools/">hollywood slots</a> vegas free slots online <a href="https://onlineslotgames.tools/">free slots</a> | https://onlineslotgames.tools/ - free slot play https://onlineslotgames.tools/ - free slot play no download

casino slots free games xlpun

free casino slots <a href="https://casinoslots.tools/">online slots free</a> free penny slots online <a href="https://casinoslots.tools/">free slot games with no download</a> | https://casinoslots.tools/ - real casino slots https://casinoslots.tools/ - doubledown casino free slots

seneca niagara casino qxqtl

mohegan sun online casino <a href="https://onlinecasinoplay24.us.org/">oxford casino</a> real casino <a href="https://onlinecasinoplay24.us.org/">penny slots</a> | https://onlinecasinoplay24.us.org/ - play casino https://onlinecasinoplay24.us.org/ - clearwater casino

gold fish casino slots qzood

free slot games with no download <a href="https://playslots.icu/">vegas free slots</a> slot machines <a href="https://playslots.icu/">online slots real money</a> | https://playslots.icu/ - caesars slots free casino https://playslots.icu/ - cleopatra slots

vegas world free slots games gnszd

simslots free slots <a href="https://casinoslot.icu/">quick hit slots</a> casino slots free games <a href="https://casinoslot.icu/">slots free</a> | https://casinoslot.icu/ - monopoly slots https://casinoslot.icu/ - wizard of oz slots

monopoly slots asclo

vegas slots online <a href="https://online-slots.fun/">free slots no download</a> penny slots <a href="https://online-slots.fun/">free slots no download</a> | https://online-slots.fun/ - casino slots free https://online-slots.fun/ - quick hit slots

pch slots tournament rwrim

free penny slots <a href="https://slotsgamesgiant.com/">celebrity slots</a> free online slots <a href="https://slotsgamesgiant.com/">cashman casino slots free</a> | https://slotsgamesgiant.com/ - free online slots games https://slotsgamesgiant.com/ - free slots vegas world

foxwoods casino online ftflb

red rock casino <a href="https://onlinecasinoasti.com/">casinos in iowa</a> belterra casino <a href="https://onlinecasinoasti.com/">hollywood online casino</a> | https://onlinecasinoasti.com/ - royal river casino https://onlinecasinoasti.com/ - sugarhouse online casino

hollywood casino online almdl

sugarhouse casino <a href="https://onlinecasino888.us.org/">online casino bonus</a> free casino games <a href="https://onlinecasino888.us.org/">hampton beach casino ballroom</a> | https://onlinecasino888.us.org/ - big fish casino https://onlinecasino888.us.org/ - paradise casino

free slots online no download zrawd

online slot games <a href="https://playslotscasino.fun/">jackpot magic slots</a> konami free slots <a href="https://playslotscasino.fun/">vegas slots online free</a> | https://playslotscasino.fun/ - free slots casino games https://playslotscasino.fun/ - slots of vegas

mountaineer casino asccn

online gambling casino <a href="https://casinorealmoney.fun/">online casinos for us players</a> high five casino slots <a href="https://casinorealmoney.fun/">chumba casino</a> | https://casinorealmoney.fun/ - doubledown casino facebook https://casinorealmoney.fun/ - sugarhouse casino

free online casino slots oaygc

real casino slots <a href="https://online-casinos.icu/">rivers casino schenectady</a> borgata online casino <a href="https://online-casinos.icu/">cherokee casino</a> | https://online-casinos.icu/ - free online casino games https://online-casinos.icu/ - turning stone casino

brian christopher slots ufjpv

brian christopher slots <a href="http://freeslots.icu/">slots farm</a> ruby slots <a href="http://freeslots.icu/">pop slots casino</a> | http://freeslots.icu/ - heart of vegas slots http://freeslots.icu/ - free slots no download

slotomania on facebook atjzn

slots free online <a href="https://slotsgameshub.com/">free penny slots online</a> slots games <a href="https://slotsgameshub.com/">penny slots</a> | https://slotsgameshub.com/ - goldfish casino slots free https://slotsgameshub.com/ - free slots slotomania

best online slots cqryf

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

play casino absyt

doubleu casino <a href="https://online-casino-games.fun/">caesars online casino</a> empire city online casino <a href="https://online-casino-games.fun/">online casino</a> | https://online-casino-games.fun/ - online casino https://online-casino-games.fun/ - doubledown casino promo codes

cleopatra slots lgmvm

vegas world free slots games <a href="https://onlineslots.tools/">vegas free slots</a> play free slots for fun <a href="https://onlineslots.tools/">da vinci diamonds free online slots</a> | https://onlineslots.tools/ - free slots hollywood https://onlineslots.tools/ - hollywood casino free slot play

harrah online casino bxzzq

usa online casino <a href="https://onlinecasinoplayslots.us.org/">online casino reviews</a> gsn casino <a href="https://onlinecasinoplayslots.us.org/">pala casino</a> | https://onlinecasinoplayslots.us.org/ - seneca allegany casino https://onlinecasinoplayslots.us.org/ - casino bonus

play slots dycxn

vegas world slots <a href="https://myslotsbuddy.com/">free online casino slots</a> vegas free slots <a href="https://myslotsbuddy.com/">free penny slots</a> | https://myslotsbuddy.com/ - play free slots for fun https://myslotsbuddy.com/ - vegas slots

slots for free noiqw

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

online gambling kcvoe

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

online gambling sites khybv

pechanga casino <a href="https://onlinecasinogamesy.us.org/">play casino</a> empire city casino online <a href="https://onlinecasinogamesy.us.org/">soaring eagle casino</a> | https://onlinecasinogamesy.us.org/ - double down casino https://onlinecasinogamesy.us.org/ - online gambling

empire city casino online iqobd

hallmark casino online <a href="https://onlinecasinogames.fun/">real casino</a> big fish casino <a href="https://onlinecasinogames.fun/">caesars casino</a> | https://onlinecasinogames.fun/ - mountaineer casino https://onlinecasinogames.fun/ - muckleshoot casino

virgin online casino dwcep

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

zone online casino games cepdv

jackpot party casino facebook <a href="https://casinoplay.fun/">pala casino online</a> slot machines <a href="https://casinoplay.fun/">caesars casino</a> | https://casinoplay.fun/ - posh casino https://casinoplay.fun/ - clearwater casino

casino slots free nrxmd

doubledown casino free slots <a href="https://playcasinoslots.icu/">doubledown casino free slots</a> pompeii slots <a href="https://playcasinoslots.icu/">free games for casino slots hollywood</a> | https://playcasinoslots.icu/ - vegas slots free https://playcasinoslots.icu/ - vegas world slots

goldfish casino slots free rdwdu

casino slots <a href="https://slotsonline.tools/">slots of vegas casino</a> hearts of vegas free slots <a href="https://slotsonline.tools/">free slot play</a> | https://slotsonline.tools/ - caesars free slots https://slotsonline.tools/ - vegas casino slots

online casinos for us players gfnys

winstar casino oklahoma <a href="https://casinoo.icu/">empire city casino</a> cafe casino online <a href="https://casinoo.icu/">parx online casino</a> | https://casinoo.icu/ - harrah cherokee casino https://casinoo.icu/ - chumba casino

slots free games rulia

cleopatra slots <a href="https://playslots.tools/">free vegas world slots</a> free slots online no download <a href="https://playslots.tools/">online slots real money</a> | https://playslots.tools/ - free vegas slots https://playslots.tools/ - cleopatra slots

free slots hollywood kizce

free casino slots games <a href="https://casino-slots.fun/">online casino slots</a> best online slots <a href="https://casino-slots.fun/">free casino slots games</a> | https://casino-slots.fun/ - da vinci diamonds free online slots https://casino-slots.fun/ - pompeii slots

caesars online casino otpig

caesar casino online slot games <a href="https://usaonlinecasinogames.us.org/">seneca allegany casino</a> prairie meadows casino <a href="https://usaonlinecasinogames.us.org/">virgin casino online nj</a> | https://usaonlinecasinogames.us.org/ - plainridge casino https://usaonlinecasinogames.us.org/ - belterra casino

free slots no download fetus

free online slots games <a href="https://casinoslotsparty.com/">free vegas slots online casino</a> pompeii slots <a href="https://casinoslotsparty.com/">slots online free</a> | https://casinoslotsparty.com/ - quick hit slots https://casinoslotsparty.com/ - slot games

hollywood casino free slot play slxle

gsn casino slots <a href="https://playcasinoslots.fun/">jackpot party casino slots</a> casino slots free games <a href="https://playcasinoslots.fun/">free online slots vegas world</a> | https://playcasinoslots.fun/ - slots games free https://playcasinoslots.fun/ - play free slots for fun

penny slots free online tfijt

quick hits free slots <a href="https://slotsgames.icu/">online casino slots</a> free online casino slots <a href="https://slotsgames.icu/">slots online</a> | https://slotsgames.icu/ - free penny slots https://slotsgames.icu/ - myvegas slots

free slots qrvqt

slots free online <a href="https://freeonlineslots.icu/">penny slots free online</a> hollywood casino free slots <a href="https://freeonlineslots.icu/">gossip slots</a> | https://freeonlineslots.icu/ - gold fish casino slots https://freeonlineslots.icu/ - play slots

san manuel online casino bnpku

meadows casino <a href="http://online-casino.icu/">mountaineer casino</a> tropicana online casino <a href="http://online-casino.icu/">pala casino</a> | http://online-casino.icu/ - sandia casino http://online-casino.icu/ - hampton beach casino ballroom

infinity slots zfmuu

caesars free slots <a href="https://vegascasino.tools/">free penny slots</a> liberty slots <a href="https://vegascasino.tools/">vegas slots free</a> | https://vegascasino.tools/ - slots free https://vegascasino.tools/ - vegas slots online free

gsn casino slots lrtit

penny slots free online <a href="https://slotsonline.fun/">goldfish casino slots free</a> online slots real money <a href="https://slotsonline.fun/">free online slots games</a> | https://slotsonline.fun/ - free slot games with no download https://slotsonline.fun/ - caesars slots

online casino real money ocrju

bigfish casino online games <a href="https://onlinecasinoasti.com/">maryland live casino online</a> caesar casino online slot games <a href="https://onlinecasinoasti.com/">sandia casino</a> | https://onlinecasinoasti.com/ - casino bonus https://onlinecasinoasti.com/ - play casino

casino online slots qxcmw

online casino reviews <a href="http://online-games.icu/">casino games slots free</a> virgin casino online nj <a href="http://online-games.icu/">vegas casino games</a> | http://online-games.icu/ - jackpot party casino http://online-games.icu/ - mohegan sun online casino

royal river casino uafzr

usa online casino <a href="https://onlinecasinoslotsmaxx.com/">parx casino online</a> foxwoods online casino login <a href="https://onlinecasinoslotsmaxx.com/">huuuge casino</a> | https://onlinecasinoslotsmaxx.com/ - vegas casino online https://onlinecasinoslotsmaxx.com/ - hollywood casino online

online slots wcclf

play slots online <a href="https://slotsgamesgiant.com/">free slots casino games</a> free vegas slots <a href="https://slotsgamesgiant.com/">mirrorball slots</a> | https://slotsgamesgiant.com/ - ruby slots https://slotsgamesgiant.com/ - online casino slots no download

Pages

Add new comment

Target Image