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 casino slots wluib

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

slots free online auywz

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

mgm online casino fcjvo

free casino games <a href="https://onlinecasino.us.org/">caesars online casino</a> casino slots <a href=" https://onlinecasino.us.org/">borgata online casino</a> | https://onlinecasino.us.org/ - best online casinos https://onlinecasino.us.org/ - casino real money

online slot games qxrja

las vegas free slots <a href="https://freeslots4k.com/">slots of vegas</a> slots online <a href="https://freeslots4k.com/">penny slots</a> | https://freeslots4k.com/ - celebrity slots https://freeslots4k.com/ - pompeii slots

online casinos for us players ypnni

high 5 casino <a href="https://playonlinecasino.us.org/">parx online casino</a> real casino | https://playonlinecasino.us.org/ - casino games

vegas slots zodir

free slot games <a href="https://slotsonline.fun/">wizard of oz slots</a> free games for casino slots hollywood <a href="https://slotsonline.fun/">pch slots tournament</a> | https://slotsonline.fun/ - mohegan sun free online slots https://slotsonline.fun/ - free slot games

real casino nkeqa

rivers casino <a href="http://online-casino.icu/">bonus casino</a> three rivers casino <a href="http://online-casino.icu/">usa online casino</a> | http://online-casino.icu/ - cafe casino online http://online-casino.icu/ - online casino games

free slots games xwked

hollywood casino free slot play <a href="https://casinoslot.icu/">free online casino slots</a> free slots machines <a href="https://casinoslot.icu/">free slots games</a> | https://casinoslot.icu/ - goldfish casino slots free https://casinoslot.icu/ - vegas world free games online slots

island view casino vdwmx

casino games slots free <a href="https://onlinecasinoplay24.us.org/">caesars casino online</a> virgin casino online nj <a href="https://onlinecasinoplay24.us.org/">river spirit casino tulsa</a> | https://onlinecasinoplay24.us.org/ - zone online casino games https://onlinecasinoplay24.us.org/ - high 5 casino

downstream casino kmgch

mohegan sun online casino <a href="https://online-casino.fun/">snoqualmie casino</a> gsn casino slots <a href="https://online-casino.fun/">hard rock casino atlantic city</a> | https://online-casino.fun/ - vegas world casino games https://online-casino.fun/ - gun lake casino

casino slots ptqat

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

chumba casino qzpvw

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

pch slots ctxjo

old vegas slots <a href="https://casinoslots.tools/">free online slots no download</a> free slots hollywood <a href="https://casinoslots.tools/">vegas world slots</a> | https://casinoslots.tools/ - vegas slots free https://casinoslots.tools/ - free slots hollywood

myvegas slots hysvw

free slots online no download <a href="https://slotsonline.fun/">free video slots</a> las vegas free slots <a href="https://slotsonline.fun/">free online slots games</a> | https://slotsonline.fun/ - jackpot party casino slots https://slotsonline.fun/ - wheel of fortune slots

rivers casino wkcql

online casinos for us players <a href="http://online-casino.icu/">foxwoods casino online slots</a> island view casino <a href="http://online-casino.icu/">thunder valley casino</a> | http://online-casino.icu/ - river spirit casino tulsa http://online-casino.icu/ - free casino

wizard of oz slots yqfsn

jackpot magic slots <a href="https://freeslots4k.com/">hollywood slots</a> free games for casino slots hollywood <a href="https://freeslots4k.com/">free casino slots</a> | https://freeslots4k.com/ - monopoly slots https://freeslots4k.com/ - free slots games online

doubledown casino free slots yidif

free slots vegas world <a href="https://casinoslot.icu/">hollywood casino online slots</a> pompeii slots <a href="https://casinoslot.icu/">mohegan sun free online slots</a> | https://casinoslot.icu/ - free slot machine games https://casinoslot.icu/ - pompeii slots

mountaineer casino ydbgm

snoqualmie casino <a href="https://onlinecasinoplay24.us.org/">firekeepers casino</a> rivers casino <a href="https://onlinecasinoplay24.us.org/">viejas casino</a> | https://onlinecasinoplay24.us.org/ - huuuge casino https://onlinecasinoplay24.us.org/ - posh casino

grand falls casino yovol

turning stone online casino <a href="https://online-casino.fun/">borgata online casino</a> choctaw casino durant oklahoma <a href="https://online-casino.fun/">casino bonus</a> | https://online-casino.fun/ - foxwoods online casino https://online-casino.fun/ - high 5 casino

online casino bonus wvgyo

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

borgata online casino scqhh

online casinos for us players <a href="https://onlinecasino.us.org/">online casino real money</a> gsn casino <a href=" https://onlinecasino.us.org/">hollywood casino</a> | https://onlinecasino.us.org/ - play casino https://onlinecasino.us.org/ - borgata online casino

gsn casino slots ompgh

casino blackjack <a href="https://playonlinecasino.us.org/">empire city online casino</a> casino real money | https://playonlinecasino.us.org/ - doubledown casino

vegas world free slots uqbsd

play slots <a href="https://casinoslots.tools/">free casino slots no download</a> konami free slots <a href="https://casinoslots.tools/">jackpot party casino slots</a> | https://casinoslots.tools/ - hot shot casino slots https://casinoslots.tools/ - free online slots no download

treasure island casino minnesota bcffj

real money casino <a href="http://online-casino.icu/">cherokee casino</a> stn play online casino <a href="http://online-casino.icu/">seneca allegany casino</a> | http://online-casino.icu/ - casinos in iowa http://online-casino.icu/ - huuuge casino

online slots free cdrgr

buffalo slots <a href="https://casinoslot.icu/">free casino slots</a> slots games <a href="https://casinoslot.icu/">cleopatra slots</a> | https://casinoslot.icu/ - free slots games online https://casinoslot.icu/ - las vegas free slots

heart of vegas slots zpewt

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

rivers casino schenectady opexv

hollywood casino online <a href="https://online-casino.fun/">high 5 casino</a> pala casino online <a href="https://online-casino.fun/">river spirit casino tulsa</a> | https://online-casino.fun/ - gsn casino slots https://online-casino.fun/ - casino bonus

blue chip casino efxee

slotomania slot machines <a href="https://onlinecasinoplay24.us.org/">best online casino</a> chumash casino <a href="https://onlinecasinoplay24.us.org/">real casino</a> | https://onlinecasinoplay24.us.org/ - harrah online casino https://onlinecasinoplay24.us.org/ - viejas casino

free online casino yyvgy

casino online <a href="https://playonlinecasino.us.org/">free online casino</a> casino play | https://playonlinecasino.us.org/ - free online casino slots

mgm online casino cdgii

empire city online casino <a href="https://onlinecasino.us.org/">high 5 casino</a> online casino bonus <a href=" https://onlinecasino.us.org/">casino play</a> | https://onlinecasino.us.org/ - best online casinos https://onlinecasino.us.org/ - zone online casino

hollywood casino online slots lpidf

cafe casino online <a href="http://online-casino.icu/">gun lake casino</a> foxwoods casino <a href="http://online-casino.icu/">chinook winds casino</a> | [url=http://online-casino.icu/]twin river casino[/url] [url=http://online-casino.icu/]mgm online casino[/url]

play free slots cgosm

wheel of fortune slots <a href="https://casinoslot.icu/">free casino slots</a> konami free slots <a href="https://casinoslot.icu/">free buffalo slots</a> | https://casinoslot.icu/ - slots free https://casinoslot.icu/ - slot machines

online casino gambling wkcth

penny slots <a href="https://online-casino.fun/">play online casino</a> foxwoods online casino login <a href="https://online-casino.fun/">high 5 casino</a> | https://online-casino.fun/ - posh casino online https://online-casino.fun/ - sugarhouse casino

free online casino slots gyjph

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

goldfish slots xzzsm

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

free casino slots ofgqo

turning stone online slots <a href="https://slotsonline.fun/">free games online slots</a> jackpot party casino slots <a href="https://slotsonline.fun/">penny slots free</a> | https://slotsonline.fun/ - hollywood casino free slots online https://slotsonline.fun/ - wizard of oz slots

borgata hotel casino qguet

pechanga casino <a href="https://onlinecasinoplay24.us.org/">island view casino</a> casino online slots <a href="https://onlinecasinoplay24.us.org/">choctaw casino</a> | https://onlinecasinoplay24.us.org/ - seneca allegany casino https://onlinecasinoplay24.us.org/ - parx casino online

gsn casino slots zixmy

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

real money casino xckkf

high 5 casino <a href="https://playonlinecasino.us.org/">winstar world casino</a> virgin online casino | https://playonlinecasino.us.org/ - zone online casino

myvegas slots xjhgg

da vinci diamonds free online slots <a href="https://slotsonline.fun/">free online slots</a> celebrity slots <a href="https://slotsonline.fun/">hollywood casino online slots free</a> | https://slotsonline.fun/ - pop slots https://slotsonline.fun/ - heart of vegas free slots

free online slots no download no registration efegk

gsn casino slots <a href="https://casinoslots.tools/">pch slots tournament</a> slots online free <a href="https://casinoslots.tools/">free games for casino slots hollywood</a> | https://casinoslots.tools/ - vegas world free games online slots https://casinoslots.tools/ - turning stone online slots

chumba casino pjcjt

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

pala casino online nj jqszc

online gambling sites <a href="https://onlinecasinoplay24.us.org/">casino games slots free</a> harrah online casino <a href="https://onlinecasinoplay24.us.org/">betfair casino online nj</a> | https://onlinecasinoplay24.us.org/ - parx online casino https://onlinecasinoplay24.us.org/ - prairie meadows casino

casino games yclmp

slots for real money <a href="https://onlinecasino.us.org/">casino bonus</a> casino online slots <a href=" https://onlinecasino.us.org/">casino online slots</a> | https://onlinecasino.us.org/ - online casino real money https://onlinecasino.us.org/ - high 5 casino

free slot games trdal

free casino slots <a href="https://playcasinoslots.icu/">vegas world free slots games</a> free slot games <a href="https://playcasinoslots.icu/">quick hits free slots</a> | https://playcasinoslots.icu/ - las vegas free slots https://playcasinoslots.icu/ - slots farm

jackpot party casino drhga

free online casino <a href="https://casinoplay.fun/">mgm online casino nj</a> clearwater casino <a href="https://casinoplay.fun/">online casinos</a> | https://casinoplay.fun/ - paradise casino https://casinoplay.fun/ - hallmark casino online

doubledown casino facebook rbpue

three rivers casino <a href="http://online-games.icu/">rock n cash casino slots</a> foxwoods casino online <a href="http://online-games.icu/">rivers casino pittsburgh</a> | http://online-games.icu/ - betfair casino online nj http://online-games.icu/ - slot machines

zone online casino reggp

play casino <a href="https://playonlinecasino.us.org/">gsn casino</a> free online casino slots | https://playonlinecasino.us.org/ - bovada casino

buffalo slots pxqke

free slots no download no registration <a href="https://playcasinoslots.icu/">house of fun free slots</a> free slots online no download no registration <a href="https://playcasinoslots.icu/">free slots casino games</a> | https://playcasinoslots.icu/ - real casino slots https://playcasinoslots.icu/ - jackpot magic slots

hollywood casino oowfm

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

Pages

Add new comment

Target Image