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

gsn casino games pwgwa

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

harrah online casino oqfmv

online casino gambling <a href="https://casinoplay.fun/">casino bonus</a> three rivers casino <a href="https://casinoplay.fun/">borgata hotel casino</a> | https://casinoplay.fun/ - parx casino https://casinoplay.fun/ - firekeepers casino

online casino games gqorr

hampton beach casino ballroom <a href="http://online-games.icu/">casino slots</a> winstar casino oklahoma <a href="http://online-games.icu/">clearwater casino</a> | http://online-games.icu/ - firekeepers casino http://online-games.icu/ - hallmark casino online

mgm online casino duxfn

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

free online slots vegas world ekzxo

free slot play <a href="https://playcasinoslots.icu/">free buffalo slots</a> liberty slots <a href="https://playcasinoslots.icu/">heart of vegas free slots</a> | https://playcasinoslots.icu/ - my vegas slots https://playcasinoslots.icu/ - hollywood casino online slots free

free slots with no download or registration rovwg

free slots hollywood <a href="https://onlineslots.tools/">jackpot party casino slots</a> free slot play no download <a href="https://onlineslots.tools/">vegas world slots</a> | https://onlineslots.tools/ - free slot games online https://onlineslots.tools/ - free slot play no download

casino games saavy

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

hollywood casino free slot play zysci

quick hits free slots <a href="https://casino-slots.fun/">free slot games online</a> goldfish slots <a href="https://casino-slots.fun/">slot machines</a> | https://casino-slots.fun/ - caesars slots https://casino-slots.fun/ - free slots games online

casino games slots free zfupc

free slots casino games <a href="https://myslotsbuddy.com/">hollywood casino free slots</a> online slots <a href="https://myslotsbuddy.com/">vegas world free games online slots</a> | https://myslotsbuddy.com/ - free slots slotomania https://myslotsbuddy.com/ - igt free slots

caesars online casino ghzhw

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

parx casino gcdpz

888 casino <a href="https://usaonlinecasinogames.us.org/">rock n cash casino slots</a> oxford casino <a href="https://usaonlinecasinogames.us.org/">casino online</a> | https://usaonlinecasinogames.us.org/ - casino play https://usaonlinecasinogames.us.org/ - slots casino games

virgin casino online nj cykuh

stn play online casino <a href="http://online-games.icu/">doubleu casino on facebook</a> double u casino <a href="http://online-games.icu/">gun lake casino</a> | http://online-games.icu/ - spirit mountain casino http://online-games.icu/ - mountaineer casino

downstream casino izurm

casino real money <a href="https://casinoplay.fun/">casino play</a> online gambling <a href="https://casinoplay.fun/">hampton beach casino ballroom</a> | https://casinoplay.fun/ - empire city online casino https://casinoplay.fun/ - northern quest casino

free online slots no download eoubf

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

naigevqa

fast cash loans <a href="https://fnq.security">loans review</a> payday loans without credit check <a href=https://fnq.security>fast loans</a>

play online casino bwoad

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

online casino slots ullkv

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

gsn casino games nsmlg

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

downstream casino wwzvo

san manuel casino <a href="http://online-games.icu/">online casino games free</a> real money casino <a href="http://online-games.icu/">casinos near me</a> | http://online-games.icu/ - online slot machines http://online-games.icu/ - casino slots

slots online yzmdy

free slots vegas <a href="https://playcasinoslots.icu/">free slots</a> old vegas slots <a href="https://playcasinoslots.icu/">free casino slots games</a> | https://playcasinoslots.icu/ - hollywood slots https://playcasinoslots.icu/ - free slot games online

high 5 casino wcjwn

hollywood casino <a href="https://casinoplay.fun/">double down casino</a> morongo casino <a href="https://casinoplay.fun/">online gambling sites</a> | https://casinoplay.fun/ - gun lake casino https://casinoplay.fun/ - foxwoods casino online

slots online csodb

free games for casino slots hollywood <a href="https://onlineslots.tools/">old vegas slots</a> casino slots free games <a href="https://onlineslots.tools/">wizard of oz slots</a> | https://onlineslots.tools/ - penny slots free https://onlineslots.tools/ - free slot play no download

foxwoods online casino pcuzc

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

real money casino lpzrl

best online casinos <a href="https://onlinecasino.us.org/">gsn casino</a> mgm online casino <a href=" https://onlinecasino.us.org/">virgin online casino</a> | https://onlinecasino.us.org/ - foxwoods online casino https://onlinecasino.us.org/ - best online casino

goldfish slots psaoy

free slots games <a href="https://myslotsbuddy.com/">world class casino slots</a> free penny slots no download <a href="https://myslotsbuddy.com/">free slots online no download</a> | https://myslotsbuddy.com/ - vegas slots https://myslotsbuddy.com/ - slots online free

vegas world free slots hcvyq

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

jackpot party casino nccnu

hard rock casino atlantic city <a href="https://usaonlinecasinogames.us.org/">casinos near me</a> hard rock casino atlantic city <a href="https://usaonlinecasinogames.us.org/">pala casino</a> | https://usaonlinecasinogames.us.org/ - online casino bonus https://usaonlinecasinogames.us.org/ - muckleshoot casino

hollywood casino fprcf

empire city online casino <a href="https://playonlinecasino.us.org/">online casino bonus</a> free online casino slots | https://playonlinecasino.us.org/ - virgin online casino

morongo casino sfzty

caesars casino <a href="http://online-games.icu/">casino online</a> rivers casino pittsburgh <a href="http://online-games.icu/">cashman casino</a> | http://online-games.icu/ - casino slot http://online-games.icu/ - maryland live casino online

doubledown casino omnrt

free online casino <a href="https://onlinecasino.us.org/">virgin online casino</a> online gambling <a href=" https://onlinecasino.us.org/">winstar world casino</a> | https://onlinecasino.us.org/ - tropicana online casino https://onlinecasino.us.org/ - foxwoods online casino

house of fun free slots lzqsn

free vegas slots online casino <a href="https://playcasinoslots.icu/">brian christopher slots</a> liberty slots casino <a href="https://playcasinoslots.icu/">free slots 777</a> | https://playcasinoslots.icu/ - free online slots https://playcasinoslots.icu/ - hearts of vegas free slots

real money casino veaig

fortune bay casino <a href="https://casinoplay.fun/">caesars casino</a> free online casino slots <a href="https://casinoplay.fun/">gsn casino on facebook</a> | https://casinoplay.fun/ - foxwoods online casino https://casinoplay.fun/ - tropicana online casino

casino online utulm

tropicana online casino <a href="https://playonlinecasino.us.org/">high 5 casino</a> play online casino | https://playonlinecasino.us.org/ - borgata online casino

online casino games free wpngp

big fish casino slots <a href="http://online-games.icu/">posh casino</a> online casino reviews <a href="http://online-games.icu/">harrah cherokee casino</a> | http://online-games.icu/ - firekeepers casino http://online-games.icu/ - slots casino games

caesars free slots online itbgy

hollywood casino online slots free <a href="https://onlineslots.tools/">slots games</a> world class casino slots <a href="https://onlineslots.tools/">slot games free</a> | https://onlineslots.tools/ - igt free slots https://onlineslots.tools/ - turning stone online slots

casino online slots bzrjq

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

casino online slots jvxay

real money casino <a href="https://onlinecasino.us.org/">free online casino</a> hollywood casino <a href=" https://onlinecasino.us.org/">online gambling</a> | https://onlinecasino.us.org/ - empire city online casino https://onlinecasino.us.org/ - empire city online casino

freeslots.com slots epclo

ruby slots <a href="https://myslotsbuddy.com/">world class casino slots</a> simslots free slots <a href="https://myslotsbuddy.com/">free slot play no download</a> | https://myslotsbuddy.com/ - free slots online https://myslotsbuddy.com/ - free slot machine games

casino play rhobs

ilani casino <a href="https://onlinecasinogames.fun/">online slot machines</a> doubleu casino on facebook <a href="https://onlinecasinogames.fun/">sugarhouse casino online</a> | https://onlinecasinogames.fun/ - jackpot party casino facebook https://onlinecasinogames.fun/ - sandia casino

mgm online casino pyffr

plainridge casino <a href="https://casinoo.icu/">riverwind casino</a> del lago casino <a href="https://casinoo.icu/">double u casino</a> | https://casinoo.icu/ - online casino games free https://casinoo.icu/ - vegas world casino games

vegas world slots odtpw

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

zone online casino games iachv

parx casino <a href="https://casinoplay.fun/">parx online casino</a> casino real money <a href="https://casinoplay.fun/">clearwater casino</a> | https://casinoplay.fun/ - casino slot https://casinoplay.fun/ - river spirit casino tulsa

free vegas slots online kkoxo

caesars slots free casino <a href="https://playcasinoslots.fun/">caesars slots</a> caesars slots <a href="https://playcasinoslots.fun/">vegas world slots</a> | https://playcasinoslots.fun/ - slots lounge https://playcasinoslots.fun/ - free slots vegas

free slots izhij

slots free <a href="https://casino-slots.fun/">hollywood casino online slots</a> cashman casino slots <a href="https://casino-slots.fun/">jackpot magic slots</a> | https://casino-slots.fun/ - heart of vegas free slots https://casino-slots.fun/ - wheel of fortune slots

empire city casino mvxro

mgm online casino <a href="http://online-casino.icu/">cafe casino online</a> vegas casino online <a href="http://online-casino.icu/">ilani casino</a> | http://online-casino.icu/ - hollywood casino online slots http://online-casino.icu/ - pechanga casino

seneca allegany casino cbefj

cherokee casino <a href="https://usaonlinecasinogames.us.org/">online casino games</a> empire city online casino <a href="https://usaonlinecasinogames.us.org/">jackpot party casino facebook</a> | https://usaonlinecasinogames.us.org/ - sugarhouse casino online https://usaonlinecasinogames.us.org/ - riverwind casino

free slots online no download siadp

pop slots <a href="https://playslots.tools/">liberty slots casino</a> free online slots no download <a href="https://playslots.tools/">penny slots</a> | https://playslots.tools/ - cashman casino slots free https://playslots.tools/ - monopoly slots

free vegas slots online ukcmw

play free slots <a href="https://slotsgames.icu/">slots free games</a> play free slots for fun <a href="https://slotsgames.icu/">slots games</a> | https://slotsgames.icu/ - infinity slots https://slotsgames.icu/ - free slots machines

casino online slots pmxxw

chumba casino <a href="https://playonlinecasino.us.org/">casino games</a> casino play | https://playonlinecasino.us.org/ - play casino

play slots ldngt

vegas world free slots <a href="https://freeonlineslots.icu/">jackpot party casino slots</a> free vegas slots <a href="https://freeonlineslots.icu/">jackpot magic slots</a> | https://freeonlineslots.icu/ - free casino slots games https://freeonlineslots.icu/ - vegas slots

Pages

Add new comment

Target Image