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

online gambling sites fqjld

meadows casino <a href="https://online-casinos.icu/">free vegas slots online casino</a> codeshareonline doubledown casino <a href="https://online-casinos.icu/">casino online</a> | https://online-casinos.icu/ - downstream casino https://online-casinos.icu/ - online slot machines

mohegan sun casino sfcqf

casino slot <a href="http://online-games.icu/">penny slots</a> doubledown casino facebook <a href="http://online-games.icu/">zone online casino games</a> | http://online-games.icu/ - real casino slots http://online-games.icu/ - hallmark casino online

free slot play no download fsjxz

free slots online <a href="https://playslots.icu/">free slot play</a> free slots with no download or registration <a href="https://playslots.icu/">hollywood casino free slots</a> | https://playslots.icu/ - online casino slots https://playslots.icu/ - liberty slots casino

heart of vegas slots hjhqr

free slot games with no download <a href="https://playslots.tools/">best online slots</a> free online casino slots <a href="https://playslots.tools/">freeslots.com slots</a> | https://playslots.tools/ - vegas free slots https://playslots.tools/ - slots online free

gold fish casino slots shoih

free slots machines <a href="https://onlineslots.tools/">buffalo slots</a> slots online <a href="https://onlineslots.tools/">slot machines</a> | https://onlineslots.tools/ - slots games https://onlineslots.tools/ - celebrity slots

free buffalo slots nduua

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

twin river casino amrkt

empire city online casino <a href="https://onlinecasino888.us.org/">mgm online casino</a> casinos near me <a href="https://onlinecasino888.us.org/">online casinos</a> | https://onlinecasino888.us.org/ - muckleshoot casino https://onlinecasino888.us.org/ - winstar casino oklahoma

play slots online dooiu

jackpot magic slots <a href="https://playcasinoslots.fun/">house of fun slots</a> monopoly slots <a href="https://playcasinoslots.fun/">free slots no download</a> | https://playcasinoslots.fun/ - free penny slots https://playcasinoslots.fun/ - cashman casino slots

posh casino guifu

online casino games free <a href="https://onlinecasinoslotsmaxx.com/">rock n cash casino slots</a> san manuel online casino <a href="https://onlinecasinoslotsmaxx.com/">grand casino hinckley</a> | https://onlinecasinoslotsmaxx.com/ - foxwoods online casino login https://onlinecasinoslotsmaxx.com/ - free online casino games

free online slots vegas world wldzy

slot games <a href="http://freeslots.icu/">real casino slots</a> play slots online <a href="http://freeslots.icu/">vegas slots</a> | http://freeslots.icu/ - slots free online http://freeslots.icu/ - slotomania free slots

mohegan sun casino xayey

turning stone online casino <a href="https://casinorealmoney.fun/">pala casino</a> san manuel casino <a href="https://casinorealmoney.fun/">hard rock casino tampa</a> | https://casinorealmoney.fun/ - turning stone casino https://casinorealmoney.fun/ - free casino games

vegas world free slots qgizf

free vegas world slots <a href="https://slotsgamesgiant.com/">slots farm</a> vegas world slots <a href="https://slotsgamesgiant.com/">slots online</a> | https://slotsgamesgiant.com/ - free slots slotomania https://slotsgamesgiant.com/ - vegas world free slots games

casino games slots free zirxn

brian christopher slots <a href="https://slotsgameshub.com/">play slots</a> free slot games with no download <a href="https://slotsgameshub.com/">goldfish slots</a> | https://slotsgameshub.com/ - ruby slots https://slotsgameshub.com/ - free slots online no download

casino games ytirk

four winds casino <a href="https://onlinecasinoplayslots.us.org/">cashman casino</a> sugarhouse online casino <a href="https://onlinecasinoplayslots.us.org/">foxwoods casino online slots</a> | https://onlinecasinoplayslots.us.org/ - belterra casino https://onlinecasinoplayslots.us.org/ - river spirit casino tulsa

best online casino bholx

online casinos <a href="https://playonlinecasino.us.org/">online casino bonus</a> zone online casino | https://playonlinecasino.us.org/ - best online casino

play online casino qougq

vegas casino games <a href="https://onlinecasinowp.us.org/">empire city casino online</a> online casino bonus <a href="https://onlinecasinowp.us.org/">casinos online</a> | https://onlinecasinowp.us.org/ - big fish casino slots https://onlinecasinowp.us.org/ - online casinos

online gambling sites jkzed

turning stone online casino <a href="https://onlinecasinoasti.com/">betfair casino online nj</a> rivers casino schenectady <a href="https://onlinecasinoasti.com/">four winds casino</a> | https://onlinecasinoasti.com/ - sandia casino https://onlinecasinoasti.com/ - gambling sites

free online slots vegas world byshd

vegas slots online free <a href="https://onlineslotgames.tools/">vegas world free games online slots</a> free vegas slots <a href="https://onlineslotgames.tools/">house of fun slots</a> | https://onlineslotgames.tools/ - online slot games https://onlineslotgames.tools/ - freeslots.com slots

gossip slots lhhsh

free penny slots online <a href="https://playslots.icu/">free slot play</a> vegas world free slots <a href="https://playslots.icu/">slots online free</a> | https://playslots.icu/ - free slots games https://playslots.icu/ - vegas slots free

pop slots vydbl

play free slots for fun <a href="https://slotswall.com/">infinity slots</a> free slots <a href="https://slotswall.com/">old vegas slots</a> | https://slotswall.com/ - free slots no download https://slotswall.com/ - casino slots

penny slots yobij

my vegas slots <a href="https://slotsonline.fun/">caesars slots free casino</a> myvegas slots <a href="https://slotsonline.fun/">play free slots for fun</a> | https://slotsonline.fun/ - pop slots https://slotsonline.fun/ - play free slots for fun

foxwoods casino hbddb

vegas casino online <a href="https://onlinecasinogames.fun/">big fish casino slots</a> rivers casino schenectady <a href="https://onlinecasinogames.fun/">zone online casino games</a> | https://onlinecasinogames.fun/ - winstar casino https://onlinecasinogames.fun/ - twin river casino

vegas slots online free rlgbh

free casino slots no download <a href="https://myslotsbuddy.com/">free slots no download no registration</a> free slots 777 <a href="https://myslotsbuddy.com/">slotomania on facebook</a> | https://myslotsbuddy.com/ - free buffalo slots https://myslotsbuddy.com/ - hollywood casino online slots

house of fun free slots blwfx

pop slots <a href="https://online-slots.fun/">casino games slots free</a> online casino slots no download <a href="https://online-slots.fun/">caesars slots</a> | https://online-slots.fun/ - ruby slots https://online-slots.fun/ - igt free slots

sugarhouse casino qvrtg

pala casino online nj <a href="https://casino-games.icu/">real money casino</a> lady luck online casino <a href="https://casino-games.icu/">three rivers casino</a> | https://casino-games.icu/ - double u casino https://casino-games.icu/ - foxwoods online casino

online gambling casino ljzui

888 casino <a href="https://usaonlinecasinogames.us.org/">firekeepers casino</a> slot machines <a href="https://usaonlinecasinogames.us.org/">online slot machines</a> | https://usaonlinecasinogames.us.org/ - plainridge casino https://usaonlinecasinogames.us.org/ - tropicana online casino

slot machine games free ixzws

free slot play <a href="https://playcasinoslots.icu/">online slots</a> free online slots no download no registration <a href="https://playcasinoslots.icu/">free games online slots</a> | https://playcasinoslots.icu/ - casino games slots free https://playcasinoslots.icu/ - play free slots for fun

vegas casino games wbtxr

mystic lake casino <a href="http://online-casino.icu/">online casino games</a> casinos near me <a href="http://online-casino.icu/">empire city casino</a> | http://online-casino.icu/ - big fish casino slots http://online-casino.icu/ - best online casino

casino slots free pimid

free casino slots games <a href="https://slotsonline.tools/">las vegas free slots</a> gsn casino slots <a href="https://slotsonline.tools/">casino games slots free</a> | https://slotsonline.tools/ - free slots hollywood https://slotsonline.tools/ - slots free

hollywood casino online slots qofdq

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

lincoln casino enjif

online casino real money <a href="https://casinoplay.fun/">pala casino</a> rivers casino schenectady <a href="https://casinoplay.fun/">real casino slots</a> | https://casinoplay.fun/ - red rock casino https://casinoplay.fun/ - vegas casino online

hollywood casino free slot play mvtul

slots lounge <a href="https://casinoslots.tools/">online slots free</a> free slots no download no registration <a href="https://casinoslots.tools/">vegas free slots</a> | https://casinoslots.tools/ - free penny slots online https://casinoslots.tools/ - casino games slots free

caesars online casino ofbmt

gsn casino games <a href="https://onlinecasinoplay24.us.org/">northern quest casino</a> san manuel online casino <a href="https://onlinecasinoplay24.us.org/">empire city online casino</a> | https://onlinecasinoplay24.us.org/ - hyper casinos https://onlinecasinoplay24.us.org/ - borgata hotel casino

choctaw casino durant oklahoma buath

bonus casino <a href="https://onlinecasinogamesy.us.org/">rock n cash casino slots</a> bigfish casino online games <a href="https://onlinecasinogamesy.us.org/">casino online slots</a> | https://onlinecasinogamesy.us.org/ - foxwoods casino online https://onlinecasinogamesy.us.org/ - bonus casino

online slots real money oyqqb

mirrorball slots <a href="https://playslotscasino.fun/">slotomania free slots</a> vegas casino slots <a href="https://playslotscasino.fun/">free slots online</a> | https://playslotscasino.fun/ - slots free games https://playslotscasino.fun/ - caesars free slots online

river spirit casino tulsa tumbq

cashman casino <a href="https://online-casino-games.fun/">hollywood casino online slots</a> hollywood casino online slots <a href="https://online-casino-games.fun/">turning stone online casino</a> | https://online-casino-games.fun/ - betfair casino online nj https://online-casino-games.fun/ - mgm online casino nj

free slots casino games dbcjq

celebrity slots <a href="https://freeonlineslots.icu/">heart of vegas slots</a> gossip slots <a href="https://freeonlineslots.icu/">las vegas free slots</a> | https://freeonlineslots.icu/ - online slots free https://freeonlineslots.icu/ - hollywood casino online slots

heart of vegas free slots qxdoo

free vegas slots online <a href="https://casinoslotsparty.com/">wizard of oz slots</a> vegas free slots online <a href="https://casinoslotsparty.com/">free slots casino games</a> | https://casinoslotsparty.com/ - penny slots https://casinoslotsparty.com/ - penny slots free online

pompeii slots bztmx

heart of vegas slots <a href="https://vegascasino.tools/">freeslots.com slots</a> konami free slots <a href="https://vegascasino.tools/">cashman casino slots free</a> | https://vegascasino.tools/ - free slots no download no registration https://vegascasino.tools/ - free casino slots

casino blackjack wlzfn

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

free slot play dcyev

free slots casino games <a href="https://playslots.tools/">igt free slots</a> las vegas free slots <a href="https://playslots.tools/">play slots online</a> | https://playslots.tools/ - penny slots free online https://playslots.tools/ - free slots casino games

gambling sites wgkjv

meadows casino <a href="https://casinoo.icu/">playmgm nj casino online</a> casinos in iowa <a href="https://casinoo.icu/">888 casino</a> | https://casinoo.icu/ - caesars casino https://casinoo.icu/ - big fish casino slots

slots of vegas casino rgbwj

play free slots for fun <a href="https://onlineslots.tools/">goldfish slots</a> quick hit slots <a href="https://onlineslots.tools/">free slots games online</a> | https://onlineslots.tools/ - free slot games https://onlineslots.tools/ - free slots games online

gambling sites gieba

casino near me <a href="https://online-casinos.icu/">caesars online casino</a> dakota sioux casino <a href="https://online-casinos.icu/">winstar world casino</a> | https://online-casinos.icu/ - choctaw casino durant oklahoma https://online-casinos.icu/ - four winds casino

pala casino online axrex

san manuel casino <a href="http://online-games.icu/">casino bonus</a> northern quest casino <a href="http://online-games.icu/">snoqualmie casino</a> | http://online-games.icu/ - hollywood casino online slots http://online-games.icu/ - dakota sioux casino

casino online ecoqy

tropicana online casino <a href="https://playonlinecasino.us.org/">free online casino</a> casino blackjack | https://playonlinecasino.us.org/ - online gambling

vegas world slots lasrp

free slots hollywood <a href="https://casinoslot.icu/">free online slots vegas world</a> free slots hollywood <a href="https://casinoslot.icu/">brian christopher slots</a> | https://casinoslot.icu/ - liberty slots https://casinoslot.icu/ - free slots machines

vegas slots usrug

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

hollywood casino online dlxms

ignition casino <a href="https://online-casino.fun/">mystic lake casino</a> casino slots <a href="https://online-casino.fun/">treasure island casino minnesota</a> | https://online-casino.fun/ - caesars online casino https://online-casino.fun/ - online gambling

chumash casino mzyoy

rock n cash casino slots <a href="https://onlinecasino888.us.org/">bonus casino</a> sugarhouse online casino <a href="https://onlinecasino888.us.org/">online casino gambling</a> | https://onlinecasino888.us.org/ - online casino reviews https://onlinecasino888.us.org/ - free online casino

Pages

Add new comment

Target Image