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

hollywood casino kfvje

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

casinos near me hhomd

mohegan sun online casino <a href="http://online-casino.icu/">online casinos</a> rivers casino <a href="http://online-casino.icu/">slot machines</a> | http://online-casino.icu/ - cashman casino http://online-casino.icu/ - casino play

codeshareonline doubledown casino scrxa

winstar casino <a href="https://online-casino.fun/">sugarhouse online casino</a> posh casino online <a href="https://online-casino.fun/">online casino slots</a> | https://online-casino.fun/ - river spirit casino tulsa https://online-casino.fun/ - real casino slots

free slots slotomania dkswq

free slots <a href="https://slotsonline.fun/">best online slots</a> free casino slots games <a href="https://slotsonline.fun/">free slot games</a> | https://slotsonline.fun/ - hollywood casino free slots online https://slotsonline.fun/ - play free slots for fun

online casino real money fjktr

lincoln casino <a href="https://onlinecasinoplay24.us.org/">zone online casino</a> choctaw casino <a href="https://onlinecasinoplay24.us.org/">winstar world casino</a> | https://onlinecasinoplay24.us.org/ - usa online casino https://onlinecasinoplay24.us.org/ - penny slots

online gambling casino kbgjw

doubledown casino <a href="https://playonlinecasino.us.org/">online casino gambling</a> casino blackjack | https://playonlinecasino.us.org/ - online casinos for us players

online gambling qvcwg

caesars online casino <a href="https://onlinecasino.us.org/">online casino</a> online casino <a href=" https://onlinecasino.us.org/">online casino gambling</a> | https://onlinecasino.us.org/ - chumba casino https://onlinecasino.us.org/ - casino play

free buffalo slots gatve

scatter slots <a href="https://casinoslot.icu/">monopoly slots</a> free online slots vegas world <a href="https://casinoslot.icu/">free casino slot games</a> | https://casinoslot.icu/ - cashman casino slots https://casinoslot.icu/ - cleopatra slots

free slots with no download or registration dwxrq

slots online <a href="https://casinoslots.tools/">doubledown casino free slots</a> vegas world free slots games <a href="https://casinoslots.tools/">house of fun slots</a> | https://casinoslots.tools/ - hollywood casino online slots https://casinoslots.tools/ - gamepoint slots

casino games fsbnc

free casino games <a href="https://playonlinecasino.us.org/">tropicana online casino</a> casino real money | https://playonlinecasino.us.org/ - online casino

chinook winds casino sdfae

snoqualmie casino <a href="http://online-casino.icu/">slot machines</a> downstream casino <a href="http://online-casino.icu/">double u casino</a> | http://online-casino.icu/ - codeshareonline doubledown casino http://online-casino.icu/ - hard rock casino atlantic city

blue chip casino hfodu

slots casino games <a href="https://online-casino.fun/">caesars online casino</a> online casino real money <a href="https://online-casino.fun/">online gambling</a> | https://online-casino.fun/ - mountaineer casino https://online-casino.fun/ - empire casino online

casino online lvtth

play casino <a href="https://playonlinecasino.us.org/">zone online casino</a> casino real money | https://playonlinecasino.us.org/ - firekeepers casino

caesars online casino xzakc

free casino games <a href="https://onlinecasino.us.org/">casino bonus</a> online casino gambling <a href=" https://onlinecasino.us.org/">bovada casino</a> | https://onlinecasino.us.org/ - real casino https://onlinecasino.us.org/ - zone online casino

buffalo slots ddiju

cleopatra slots <a href="https://freeslots4k.com/">infinity slots</a> turning stone online slots <a href="https://freeslots4k.com/">free casino slots</a> | https://freeslots4k.com/ - play free slots for fun https://freeslots4k.com/ - free slots games

free online slots no download no registration sjwkl

scatter slots <a href="https://slotsonline.fun/">vegas world free slots</a> casino slots free games <a href="https://slotsonline.fun/">slots for free</a> | https://slotsonline.fun/ - cashman casino slots https://slotsonline.fun/ - cashman casino slots

hollywood online casino jglss

caesars online casino <a href="https://onlinecasinoplay24.us.org/">dakota sioux casino</a> mystic lake casino <a href="https://onlinecasinoplay24.us.org/">borgata hotel casino</a> | https://onlinecasinoplay24.us.org/ - mohegan sun online casino https://onlinecasinoplay24.us.org/ - hollywood casino play4fun

free video slots oqpfj

liberty slots <a href="https://casinoslot.icu/">free slot play</a> slots lounge <a href="https://casinoslot.icu/">free slot games online</a> | https://casinoslot.icu/ - caesars free slots https://casinoslot.icu/ - vegas free slots

firekeepers casino mbiyh

riverwind casino <a href="http://online-casino.icu/">mystic lake casino</a> usa online casino <a href="http://online-casino.icu/">online casino real money</a> | http://online-casino.icu/ - 888 casino http://online-casino.icu/ - chinook winds casino

high 5 casino rmawu

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

vegas slots online jnekj

ruby slots <a href="https://casinoslots.tools/">free vegas world slots</a> free slot games online <a href="https://casinoslots.tools/">free slot games with no download</a> | https://casinoslots.tools/ - free slots with no download or registration https://casinoslots.tools/ - online slots free

casino real money yfxal

bovada casino <a href="https://onlinecasino.us.org/">gsn casino games</a> casino blackjack <a href=" https://onlinecasino.us.org/">online casino games</a> | https://onlinecasino.us.org/ - online gambling casino https://onlinecasino.us.org/ - hollywood casino

tropicana online casino wnzgu

winstar casino <a href="https://online-casino.fun/">river spirit casino tulsa</a> sugarhouse online casino <a href="https://online-casino.fun/">casino online</a> | https://online-casino.fun/ - casino play https://online-casino.fun/ - foxwoods online casino login

casino slots gnfro

bovada casino <a href="https://playonlinecasino.us.org/">online casinos</a> casino online slots | https://playonlinecasino.us.org/ - mgm online casino

free vegas slots online jwips

free online slots no download no registration <a href="https://slotsonline.fun/">free penny slots no download</a> free slot play no download <a href="https://slotsonline.fun/">slots lounge</a> | https://slotsonline.fun/ - best online slots https://slotsonline.fun/ - free slots vegas

turning stone casino ezpjk

zone online casino <a href="https://onlinecasinoplay24.us.org/">hollywood casino</a> rock n cash casino slots <a href="https://onlinecasinoplay24.us.org/">san manuel casino</a> | https://onlinecasinoplay24.us.org/ - casino online slots https://onlinecasinoplay24.us.org/ - hollywood casino play4fun

pop slots gadje

heart of vegas slots <a href="https://freeslots4k.com/">free slots online no download no registration</a> vegas slots free <a href="https://freeslots4k.com/">free slots 777</a> | https://freeslots4k.com/ - slots games https://freeslots4k.com/ - caesars slots

slots free games ojmbl

online slots free <a href="https://casinoslot.icu/">penny slots free</a> free casino slot games <a href="https://casinoslot.icu/">free vegas slots</a> | https://casinoslot.icu/ - slot machine games free https://casinoslot.icu/ - freeslots.com slots

stn play online casino jmzoz

posh casino <a href="http://online-casino.icu/">pala casino online</a> blue chip casino <a href="http://online-casino.icu/">sugarhouse online casino</a> | http://online-casino.icu/ - chinook winds casino http://online-casino.icu/ - caesars casino

casino blackjack bfnle

online casino gambling <a href="https://onlinecasino.us.org/">best online casinos</a> online casinos <a href=" https://onlinecasino.us.org/">gsn casino</a> | https://onlinecasino.us.org/ - casino bonus https://onlinecasino.us.org/ - caesars online casino

mgm online casino oqere

firekeepers casino <a href="https://playonlinecasino.us.org/">best online casinos</a> casino slots | https://playonlinecasino.us.org/ - real money casino

free casino slots no download jvcwt

free penny slots no download <a href="https://casinoslots.tools/">slots lounge</a> penny slots free online <a href="https://casinoslots.tools/">caesars slots free casino</a> | https://casinoslots.tools/ - wizard of oz slots https://casinoslots.tools/ - online slots real money

soaring eagle casino diubh

playmgm nj casino online <a href="https://online-casino.fun/">playmgm nj casino online</a> tropicana online casino <a href="https://online-casino.fun/">sugarhouse online casino</a> | https://online-casino.fun/ - pala casino online nj https://online-casino.fun/ - online gambling

casino slots uyhlb

hollywood casino <a href="https://playonlinecasino.us.org/">slots for real money</a> doubledown casino | https://playonlinecasino.us.org/ - real casino

monopoly slots pvclw

cleopatra slots <a href="https://slotsonline.fun/">caesars slots free casino</a> slots online free <a href="https://slotsonline.fun/">free slots games online</a> | https://slotsonline.fun/ - free video slots https://slotsonline.fun/ - pop slots casino

play casino vmyot

chumba casino <a href="https://onlinecasino.us.org/">free online casino</a> caesars online casino <a href=" https://onlinecasino.us.org/">hollywood casino</a> | https://onlinecasino.us.org/ - hollywood casino https://onlinecasino.us.org/ - online gambling

posh casino viugs

vegas casino online <a href="http://online-casino.icu/">rock n cash casino slots</a> bigfish casino online games <a href="http://online-casino.icu/">four winds casino</a> | http://online-casino.icu/ - posh casino http://online-casino.icu/ - belterra casino

choctaw casino zpxfk

slot machines <a href="https://onlinecasinoplay24.us.org/">casino bonus</a> hollywood casino online <a href="https://onlinecasinoplay24.us.org/">casino online slots</a> | https://onlinecasinoplay24.us.org/ - potawatomi casino https://onlinecasinoplay24.us.org/ - royal river casino

online casino slots qkirv

play slots <a href="https://casinoslot.icu/">gold fish casino slots</a> cleopatra slots <a href="https://casinoslot.icu/">casino games slots free</a> | https://casinoslot.icu/ - free slot games online https://casinoslot.icu/ - free games for casino slots hollywood

high 5 casino zqhwd

hollywood casino <a href="https://playonlinecasino.us.org/">slots for real money</a> casino blackjack | https://playonlinecasino.us.org/ - online casino real money

rivers casino schenectady medua

gsn casino games <a href="https://online-casino.fun/">empire city online casino</a> gsn casino games <a href="https://online-casino.fun/">casino bonus</a> | https://online-casino.fun/ - gsn casino games https://online-casino.fun/ - foxwoods casino

hollywood casino online slots free ijjkw

caesars free slots online <a href="https://freeslots4k.com/">mirrorball slots</a> myvegas slots <a href="https://freeslots4k.com/">online slots real money</a> | https://freeslots4k.com/ - doubledown casino free slots https://freeslots4k.com/ - slots online

free slots vegas world bceyq

jackpot magic slots <a href="https://casinoslots.tools/">vegas world free games online slots</a> free slot games <a href="https://casinoslots.tools/">pop slots casino</a> | https://casinoslots.tools/ - cashman casino slots https://casinoslots.tools/ - gsn casino slots

online casino bonus yjfkw

online gambling <a href="https://onlinecasino.us.org/">hollywood casino</a> borgata online casino <a href=" https://onlinecasino.us.org/">free online casino</a> | https://onlinecasino.us.org/ - online casinos https://onlinecasino.us.org/ - play casino

mgm online casino cwrmj

online casino real money <a href="https://playonlinecasino.us.org/">online casino real money</a> bovada casino | https://playonlinecasino.us.org/ - online casino bonus

house of fun slots bwsoy

gsn casino slots <a href="https://slotsonline.fun/">vegas world slots</a> vegas world free slots <a href="https://slotsonline.fun/">caesars slots free casino</a> | https://slotsonline.fun/ - free video slots https://slotsonline.fun/ - free slots no download no registration

free online casino slots uawpb

casino near me <a href="http://online-casino.icu/">choctaw casino</a> casino online <a href="http://online-casino.icu/">online casinos</a> | http://online-casino.icu/ - red rock casino http://online-casino.icu/ - parx casino online

wheel of fortune slots ztnjb

online casino slots <a href="https://casinoslot.icu/">casino games slots free</a> house of fun slots <a href="https://casinoslot.icu/">pop slots</a> | https://casinoslot.icu/ - play free slots for fun https://casinoslot.icu/ - mohegan sun free online slots

sugarhouse online casino lnouw

casino online <a href="https://onlinecasinoplay24.us.org/">plainridge casino</a> big fish casino <a href="https://onlinecasinoplay24.us.org/">slots for real money</a> | https://onlinecasinoplay24.us.org/ - jack online casino https://onlinecasinoplay24.us.org/ - hard rock casino atlantic city

online casino gambling zlibr

doubleu casino on facebook <a href="https://online-casino.fun/">online casino bonus</a> fortune bay casino <a href="https://online-casino.fun/">casino games slots free</a> | https://online-casino.fun/ - double down casino https://online-casino.fun/ - morongo casino

Pages

Add new comment

Target Image