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

zone online casino otyku

free vegas slots online casino <a href="https://freecasinogames.icu/">ignition casino</a> four winds casino <a href="https://freecasinogames.icu/">cherokee casino</a> | https://freecasinogames.icu/ - borgata online casino https://freecasinogames.icu/ - downstream casino

hollywood online casino hpvep

twin river casino <a href="https://casinoo.ooo/">seneca allegany casino</a> real casino slots <a href="https://casinoo.ooo/">casino games</a> | https://casinoo.ooo/ - winstar casino oklahoma https://casinoo.ooo/ - free online casino

free casino hakfh

free casino games vegas world <a href="https://onlinecasinorealmoney.ooo/">riverwind casino</a> prairie meadows casino <a href="https://onlinecasinorealmoney.ooo/">island view casino</a> | https://onlinecasinorealmoney.ooo/ - maryland live casino online https://onlinecasinorealmoney.ooo/ - caesars casino

rivers casino ldlqy

play casino <a href="https://casinorealmoney.ooo/">free vegas casino games</a> paradise casino <a href="https://casinorealmoney.ooo/">free casino slot games</a> | https://casinorealmoney.ooo/ - online casino slots https://casinorealmoney.ooo/ - san manuel online casino

big fish casino kwxxx

cafe casino online <a href="https://playcasinogames.icu/">belterra casino</a> empire city online casino <a href="https://playcasinogames.icu/">prairie meadows casino</a> | https://playcasinogames.icu/ - slot machines https://playcasinogames.icu/ - sugarhouse online casino

spirit mountain casino kbyvh

casino slots <a href="https://playslots.ooo/">chinook winds casino</a> red rock casino <a href="https://playslots.ooo/">san manuel casino</a> | https://playslots.ooo/ - parx casino online https://playslots.ooo/ - fortune bay casino

caesars slots ucrhk

jackpot party casino slots <a href="https://onlinecasinoslots.icu/">free slots no download no registration</a> slots of vegas casino <a href="https://onlinecasinoslots.icu/">gold fish casino slots</a> | https://onlinecasinoslots.icu/ - vegas casino slots https://onlinecasinoslots.icu/ - liberty slots

grand casino hinckley dwcyy

mgm online casino nj <a href="https://onlinecasinoo.icu/">online slot machines</a> island view casino <a href="https://onlinecasinoo.icu/">rock n cash casino slots</a> | https://onlinecasinoo.icu/ - doubledown casino facebook https://onlinecasinoo.icu/ - play free vegas casino games

royal river casino vaixa

cashman casino <a href="https://realmoneycasino.icu/">caesars online casino</a> winstar casino <a href="https://realmoneycasino.icu/">bonus casino</a> | https://realmoneycasino.icu/ - casino online slots https://realmoneycasino.icu/ - sugarhouse online casino

casino blackjack qucog

free casino games online <a href="https://casino-bonus.icu/">gsn casino on facebook</a> casino real money <a href="https://casino-bonus.icu/">big fish casino</a> | https://casino-bonus.icu/ - lady luck online casino https://casino-bonus.icu/ - bonus casino

free slots vegas ikekr

turning stone online slots <a href="https://casinoslotsonline.icu/">casino slots free games</a> free slots <a href="https://casinoslotsonline.icu/">vegas casino slots</a> | https://casinoslotsonline.icu/ - turning stone online slots https://casinoslotsonline.icu/ - free video slots

casino slots free ecaml

best online slots <a href="https://casinoslotsplay.icu/">online slots</a> slots games free <a href="https://casinoslotsplay.icu/">free slots with no download or registration</a> | https://casinoslotsplay.icu/ - free games for casino slots hollywood https://casinoslotsplay.icu/ - free vegas slots online casino

winstar world casino bswuh

casino games <a href="https://realmoneycasino.ooo/">ignition casino</a> play casino <a href="https://realmoneycasino.ooo/">snoqualmie casino</a> | https://realmoneycasino.ooo/ - river spirit casino tulsa https://realmoneycasino.ooo/ - las vegas casinos

winstar casino ewveg

prairie meadows casino <a href="https://freeonlineslots.ooo/">free vegas slots online casino</a> empire city casino online <a href="https://freeonlineslots.ooo/">real money casino</a> | https://freeonlineslots.ooo/ - free casino games online https://freeonlineslots.ooo/ - lincoln casino

casino slots free games ptydk

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

slot games free rcmaj

free buffalo slots <a href="https://slotonline.icu/">free slots online</a> free slots slotomania <a href="https://slotonline.icu/">igt free slots</a> | https://slotonline.icu/ - cashman casino slots https://slotonline.icu/ - free slots 777

vegas slots online free ipmmp

real casino slots <a href="https://realmoneyslots.icu/">slots free online</a> free slots 777 <a href="https://realmoneyslots.icu/">free online slots</a> | https://realmoneyslots.icu/ - jackpot party casino slots https://realmoneyslots.icu/ - caesars free slots

cashman casino hdlfz

maryland live casino online <a href="https://casinoplay.icu/">downstream casino</a> big fish casino slots <a href="https://casinoplay.icu/">free online slots</a> | https://casinoplay.icu/ - lincoln casino https://casinoplay.icu/ - seneca allegany casino

brian christopher slots qipdb

free slot play no download <a href="https://playslotsonline.icu/">gamepoint slots</a> free slots hollywood <a href="https://playslotsonline.icu/">jackpot party casino slots</a> | https://playslotsonline.icu/ - hearts of vegas free slots https://playslotsonline.icu/ - free slots games

online casino bonus kmjzh

zone online casino <a href="https://vegascasino.icu/">online casino gambling</a> island view casino <a href="https://vegascasino.icu/">free casino games</a> | https://vegascasino.icu/ - slot machines https://vegascasino.icu/ - casinos online

online casino gambling ogftt

winstar casino <a href="https://onlinecasinoslots.ooo/">free casino games slotomania</a> casino game <a href="https://onlinecasinoslots.ooo/">meadows casino</a> | https://onlinecasinoslots.ooo/ - gsn casino on facebook https://onlinecasinoslots.ooo/ - online casinos for us players

cashman casino slots qhpva

heart of vegas free slots <a href="https://freeslots24.icu/">heart of vegas slots</a> charlestown races and slots <a href="https://freeslots24.icu/">slots online free</a> | https://freeslots24.icu/ - slots games https://freeslots24.icu/ - mohegan sun free online slots

borgata hotel casino ttyuc

rivers casino schenectady <a href="https://casinorealmoney.icu/">las vegas casinos</a> gun lake casino <a href="https://casinorealmoney.icu/">virgin casino online</a> | https://casinorealmoney.icu/ - borgata hotel casino https://casinorealmoney.icu/ - ilani casino

meadows casino hxllm

doubleu casino <a href="https://casinovegas.ooo/">casino games</a> jack online casino <a href="https://casinovegas.ooo/">sugarhouse casino</a> | https://casinovegas.ooo/ - prairie meadows casino https://casinovegas.ooo/ - play online casino

slots lounge shioq

cashman casino slots <a href="https://slotsrealmoney.icu/">old vegas slots</a> free slots <a href="https://slotsrealmoney.icu/">free buffalo slots</a> | https://slotsrealmoney.icu/ - monopoly slots https://slotsrealmoney.icu/ - free slot games with no download

empire city casino yszsm

hard rock casino atlantic city <a href="https://playonlinecasino.icu/">prairie meadows casino</a> vegas casino games <a href="https://playonlinecasino.icu/">casino blackjack</a> | https://playonlinecasino.icu/ - virgin casino online https://playonlinecasino.icu/ - virgin casino online nj

sugarhouse casino gcozp

potawatomi casino <a href="https://playcasinoslots.ooo/">casino near me</a> winstar world casino <a href="https://playcasinoslots.ooo/">three rivers casino</a> | https://playcasinoslots.ooo/ - gsn casino games https://playcasinoslots.ooo/ - online gambling sites

sandia casino ilyso

cherokee casino <a href="https://slotsgames.ooo/">mgm online casino nj</a> no deposit casino <a href="https://slotsgames.ooo/">high five casino slots</a> | https://slotsgames.ooo/ - best online casino https://slotsgames.ooo/ - empire casino online

golden nugget online casino ufexn

empire city online casino <a href="https://onlinecasinoplay.icu/">casino online</a> hollywood online casino <a href="https://onlinecasinoplay.icu/">choctaw casino</a> | https://onlinecasinoplay.icu/ - hypercasinos https://onlinecasinoplay.icu/ - casino games slots free

free slot games nitmm

free slots hollywood <a href="https://slotgames.icu/">scatter slots</a> free slots online no download <a href="https://slotgames.icu/">cashman casino slots</a> | https://slotgames.icu/ - casino slots free games https://slotgames.icu/ - liberty slots casino

free online slots no download no registration llzpy

doubledown casino free slots <a href="https://vegasslotsonline.icu/">caesars slots</a> hollywood casino online slots free <a href="https://vegasslotsonline.icu/">free slots vegas world</a> | https://vegasslotsonline.icu/ - slots online https://vegasslotsonline.icu/ - hearts of vegas free slots

hollywood casino play4fun luuug

three rivers casino <a href="https://slotsonline.ooo/">foxwoods casino online</a> hypercasinos <a href="https://slotsonline.ooo/">online gambling</a> | https://slotsonline.ooo/ - casino online https://slotsonline.ooo/ - cherokee casino

empire city casino online onzrz

big fish casino <a href="https://freeonlinecasino.icu/">choctaw casino durant oklahoma</a> lincoln casino <a href="https://freeonlinecasino.icu/">slotomania slot machines</a> | https://freeonlinecasino.icu/ - casino games free online https://freeonlinecasino.icu/ - free casino games no download

heart of vegas free slots eiwxd

las vegas free slots <a href="https://freecasinoslots.icu/">slots free online</a> slots for free <a href="https://freecasinoslots.icu/">online casino slots no download</a> | https://freecasinoslots.icu/ - hearts of vegas free slots https://freecasinoslots.icu/ - world class casino slots

virgin casino online nj zjwri

casino games online <a href="https://freecasinogames.icu/">mgm online casino nj</a> real money casino <a href="https://freecasinogames.icu/">slots casino games</a> | https://freecasinogames.icu/ - real casino slots https://freecasinogames.icu/ - bonus casino

empire city casino online tsomi

casino games <a href="https://usacasino.ooo/">foxwoods casino</a> online casino slots <a href="https://usacasino.ooo/">grand falls casino</a> | https://usacasino.ooo/ - best online casino https://usacasino.ooo/ - posh casino online

big fish casino hjgdr

chinook winds casino <a href="https://onlinecasinox.icu/">virgin casino online nj</a> free vegas slots online casino <a href="https://onlinecasinox.icu/">mgm online casino nj</a> | https://onlinecasinox.icu/ - real casino slots https://onlinecasinox.icu/ - thunder valley casino

winstar casino oklahoma jixhn

paradise casino <a href="https://onlinecasinorealmoney.ooo/">free online casino</a> winstar world casino <a href="https://onlinecasinorealmoney.ooo/">sugarhouse casino</a> | https://onlinecasinorealmoney.ooo/ - gambling sites https://onlinecasinorealmoney.ooo/ - casino game

rock n cash casino slots qftvj

mohegan sun online casino <a href="https://casinoo.ooo/">chumash casino</a> del lago casino <a href="https://casinoo.ooo/">caesars casino online</a> | https://casinoo.ooo/ - posh casino online https://casinoo.ooo/ - hollywood online casino

bigfish casino online games khutj

borgata online casino <a href="https://playcasinogames.icu/">real money casino</a> free casino <a href="https://playcasinogames.icu/">casino play</a> | https://playcasinogames.icu/ - choctaw casino durant oklahoma https://playcasinogames.icu/ - empire city casino online

big fish casino xrglj

del lago casino <a href="https://casinorealmoney.ooo/">winstar casino oklahoma</a> betfair casino online nj <a href="https://casinorealmoney.ooo/">casino bonus</a> | https://casinorealmoney.ooo/ - online gambling sites https://casinorealmoney.ooo/ - jack online casino

no deposit casino ebiai

lady luck online casino <a href="https://playslots.ooo/">caesars casino</a> foxwoods casino <a href="https://playslots.ooo/">online casino</a> | https://playslots.ooo/ - high five casino slots https://playslots.ooo/ - vegas casino online

bovada casino wpkrn

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

celebrity slots pawkf

free casino slots games <a href="https://onlinecasinoslots.icu/">online slots</a> hollywood slots <a href="https://onlinecasinoslots.icu/">buffalo gold slots</a> | https://onlinecasinoslots.icu/ - world class casino slots https://onlinecasinoslots.icu/ - free online casino slots

mohegan sun casino kwezc

doubleu casino <a href="https://casino-bonus.icu/">casinos near me</a> casino real money <a href="https://casino-bonus.icu/">golden nugget online casino</a> | https://casino-bonus.icu/ - winstar casino https://casino-bonus.icu/ - play online casino

cafe casino online aevmg

high five casino slots <a href="https://realmoneycasino.icu/">greektown casino</a> empire city casino <a href="https://realmoneycasino.icu/">real casino slots</a> | https://realmoneycasino.icu/ - free vegas slots online casino https://realmoneycasino.icu/ - free casino

playmgm nj casino online jducc

ilani casino <a href="https://onlinecasinoo.icu/">zone online casino games</a> vegas world casino games <a href="https://onlinecasinoo.icu/">double down casino</a> | https://onlinecasinoo.icu/ - free casino games slots https://onlinecasinoo.icu/ - muckleshoot casino

vegas casino slots mblbo

free online casino slots <a href="https://casinoslotsplay.icu/">gsn casino slots</a> vegas world free slots <a href="https://casinoslotsplay.icu/">online casino slots no download</a> | https://casinoslotsplay.icu/ - my vegas slots https://casinoslotsplay.icu/ - online slots

free slots with no download or registration xevbd

play free slots <a href="https://casinoslotsonline.icu/">jackpot party casino slots</a> penny slots <a href="https://casinoslotsonline.icu/">slot machine games free</a> | https://casinoslotsonline.icu/ - house of fun slots https://casinoslotsonline.icu/ - house of fun free slots

empire casino online vkucc

hypercasinos <a href="https://realmoneycasino.ooo/">choctaw casino</a> free vegas casino games <a href="https://realmoneycasino.ooo/">play casino</a> | https://realmoneycasino.ooo/ - sugarhouse online casino https://realmoneycasino.ooo/ - codeshareonline doubledown casino

Pages

Add new comment

Target Image