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

free slots no download bcbrm

hollywood slots <a href="https://onlineslotgames.tools/">hot shot casino slots</a> brian christopher slots <a href="https://onlineslotgames.tools/">scatter slots</a> | https://onlineslotgames.tools/ - slots online free https://onlineslotgames.tools/ - simslots free slots

vegas world free slots amnin

free slots online no download <a href="http://freeslots.icu/">caesars free slots</a> mirrorball slots <a href="http://freeslots.icu/">hollywood casino online slots free</a> | http://freeslots.icu/ - hollywood casino online slots free http://freeslots.icu/ - vegas world free games online slots

free casino slots no download yoguc

online casino slots <a href="https://onlineslots.tools/">slots free games</a> penny slots free online <a href="https://onlineslots.tools/">penny slots free</a> | https://onlineslots.tools/ - slotomania on facebook https://onlineslots.tools/ - hot shot casino slots

free slots hollywood qmpoy

free slot games with no download <a href="https://online-slots.fun/">turning stone online slots</a> gamepoint slots <a href="https://online-slots.fun/">free casino slots</a> | https://online-slots.fun/ - wizard of oz slots https://online-slots.fun/ - free buffalo slots

lincoln casino czhty

empire city casino online <a href="https://onlinecasinoplay24.us.org/">doubleu casino</a> del lago casino <a href="https://onlinecasinoplay24.us.org/">foxwoods online casino login</a> | https://onlinecasinoplay24.us.org/ - foxwoods casino online https://onlinecasinoplay24.us.org/ - empire city casino

clearwater casino rssak

mystic lake casino <a href="https://online-casino-games.fun/">slots casino games</a> gsn casino slots <a href="https://online-casino-games.fun/">real money casino</a> | https://online-casino-games.fun/ - virgin casino online nj https://online-casino-games.fun/ - borgata hotel casino

seneca niagara casino oxaqi

empire city casino online <a href="https://online-casino.fun/">borgata online casino</a> empire city casino online <a href="https://online-casino.fun/">three rivers casino</a> | https://online-casino.fun/ - casino online https://online-casino.fun/ - parx online casino

online casino slots no download ljfsc

hollywood slots <a href="https://casinoslots.tools/">free casino slots games</a> free slots hollywood <a href="https://casinoslots.tools/">slotomania on facebook</a> | https://casinoslots.tools/ - slots online free https://casinoslots.tools/ - heart of vegas slots

free online slots no download no registration hvuta

free slots vegas <a href="https://playslots.icu/">hollywood casino online slots</a> liberty slots <a href="https://playslots.icu/">online slots</a> | https://playslots.icu/ - las vegas free slots https://playslots.icu/ - house of fun slots

rivers casino pittsburgh qxodj

gsn casino games <a href="https://onlinecasinogames.fun/">vegas casino online</a> double down casino <a href="https://onlinecasinogames.fun/">big fish casino</a> | https://onlinecasinogames.fun/ - slots casino games https://onlinecasinogames.fun/ - treasure island casino minnesota

play free slots for fun tgcrq

vegas slots free <a href="https://casinoslot.icu/">best online slots</a> caesars slots <a href="https://casinoslot.icu/">slots lounge</a> | https://casinoslot.icu/ - vegas world free slots https://casinoslot.icu/ - slots online free

scatter slots bplls

freeslots.com slots <a href="https://casinoslotsparty.com/">charlestown races and slots</a> celebrity slots <a href="https://casinoslotsparty.com/">hollywood casino free slots online</a> | https://casinoslotsparty.com/ - slotomania free slots https://casinoslotsparty.com/ - free casino slots

casino near me zyvbi

hollywood casino play4fun <a href="https://onlinecasinogamesy.us.org/">double u casino</a> plainridge casino <a href="https://onlinecasinogamesy.us.org/">real casino slots</a> | https://onlinecasinogamesy.us.org/ - blue chip casino https://onlinecasinogamesy.us.org/ - doubledown casino

hollywood casino free slots online kzamc

free vegas slots <a href="https://playslots.tools/">vegas slots online</a> caesars free slots <a href="https://playslots.tools/">casino slots free</a> | https://playslots.tools/ - heart of vegas free slots https://playslots.tools/ - quick hit slots

play free slots mzddt

goldfish casino slots free <a href="https://playcasinoslots.icu/">my vegas slots</a> pch slots tournament <a href="https://playcasinoslots.icu/">online slots free</a> | https://playcasinoslots.icu/ - wizard of oz slots https://playcasinoslots.icu/ - free online slots no download

turning stone online slots xayjy

free online slots no download <a href="https://slotswall.com/">online casino slots</a> vegas slots <a href="https://slotswall.com/">hollywood casino free slots</a> | https://slotswall.com/ - liberty slots casino https://slotswall.com/ - free slots with no download or registration

big fish casino oxhsp

doubledown casino promo codes <a href="https://casinoplay.fun/">online gambling</a> real casino slots <a href="https://casinoplay.fun/">online gambling sites</a> | https://casinoplay.fun/ - online casino slots https://casinoplay.fun/ - island view casino

doubledown casino kusen

best online casino <a href="https://onlinecasino.us.org/">free online casino slots</a> play casino <a href=" https://onlinecasino.us.org/">online casino gambling</a> | https://onlinecasino.us.org/ - online gambling casino https://onlinecasino.us.org/ - winstar world casino

chumba casino ifesz

online casinos <a href="https://usaonlinecasinogames.us.org/">plainridge casino</a> sugarhouse online casino <a href="https://usaonlinecasinogames.us.org/">caesars casino</a> | https://usaonlinecasinogames.us.org/ - parx casino https://usaonlinecasinogames.us.org/ - play casino

free penny slots qtwqs

hot shot casino slots <a href="https://casino-slots.fun/">free online casino slots</a> da vinci diamonds free online slots <a href="https://casino-slots.fun/">slot games free</a> | https://casino-slots.fun/ - house of fun free slots https://casino-slots.fun/ - mirrorball slots

gamepoint slots vatxp

slots of vegas casino <a href="https://slotsonline.tools/">hollywood casino online slots</a> casino slots <a href="https://slotsonline.tools/">hearts of vegas free slots</a> | https://slotsonline.tools/ - free slots machines https://slotsonline.tools/ - free vegas slots

pala casino mdcun

parx casino online <a href="https://casinoo.icu/">jackpot party casino facebook</a> online casino slots <a href="https://casinoo.icu/">online casino games free</a> | https://casinoo.icu/ - posh casino online https://casinoo.icu/ - codeshareonline doubledown casino

slot machine games free tocck

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

free online slots no download vdkdi

free slots games online <a href="https://playcasinoslots.fun/">free slots casino games</a> vegas slots free <a href="https://playcasinoslots.fun/">free online slots no download no registration</a> | https://playcasinoslots.fun/ - wizard of oz slots https://playcasinoslots.fun/ - las vegas free slots

liberty slots twfnd

vegas slots <a href="https://slotsgameshub.com/">cashman casino slots free</a> free slot games with no download <a href="https://slotsgameshub.com/">free vegas slots online casino</a> | https://slotsgameshub.com/ - free slots vegas https://slotsgameshub.com/ - gossip slots

buffalo slots kkpxb

wheel of fortune slots <a href="https://vegascasino.tools/">liberty slots</a> scatter slots <a href="https://vegascasino.tools/">scatter slots</a> | https://vegascasino.tools/ - free slot games https://vegascasino.tools/ - goldfish slots

slots for free efhxi

gamepoint slots <a href="https://slotsgamesgiant.com/">slotomania free slots</a> world class casino slots <a href="https://slotsgamesgiant.com/">free slots no download</a> | https://slotsgamesgiant.com/ - jackpot magic slots https://slotsgamesgiant.com/ - slots free games

casino online gezso

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

caesars free slots kjdwv

vegas free slots <a href="https://slotsgames.icu/">slotomania free slots</a> gamepoint slots <a href="https://slotsgames.icu/">slots games</a> | https://slotsgames.icu/ - play slots online https://slotsgames.icu/ - free games for casino slots hollywood

online gambling sites fgbvw

chumash casino <a href="http://online-games.icu/">doubledown casino</a> sandia casino <a href="http://online-games.icu/">rock n cash casino slots</a> | http://online-games.icu/ - three rivers casino http://online-games.icu/ - lady luck online casino

caesar casino online slot games gjcyi

casinos near me <a href="https://onlinecasinoplayslots.us.org/">bonus casino</a> san manuel casino <a href="https://onlinecasinoplayslots.us.org/">double u casino</a> | https://onlinecasinoplayslots.us.org/ - pechanga casino https://onlinecasinoplayslots.us.org/ - penny slots

empire city online casino ksnqj

888 casino <a href="https://onlinecasinoasti.com/">four winds casino</a> pala casino online <a href="https://onlinecasinoasti.com/">potawatomi casino</a> | https://onlinecasinoasti.com/ - posh casino online https://onlinecasinoasti.com/ - gsn casino on facebook

cashman casino slots bfntw

free online slots games <a href="https://myslotsbuddy.com/">gossip slots</a> cashman casino slots <a href="https://myslotsbuddy.com/">free online slots no download</a> | https://myslotsbuddy.com/ - hollywood casino free slots https://myslotsbuddy.com/ - pompeii slots

rock n cash casino slots fhzwk

hollywood casino online slots <a href="https://onlinecasinoslotsmaxx.com/">sugarhouse casino online</a> ignition casino <a href="https://onlinecasinoslotsmaxx.com/">del lago casino</a> | https://onlinecasinoslotsmaxx.com/ - slotomania slot machines https://onlinecasinoslotsmaxx.com/ - casino real money

slots farm imceq

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

slots free online fabwt

slot machine games free <a href="https://playslots.icu/">penny slots free</a> hollywood casino online slots free <a href="https://playslots.icu/">free slots hollywood</a> | https://playslots.icu/ - free penny slots online https://playslots.icu/ - slotomania on facebook

heart of vegas slots ynxgu

pch slots tournament <a href="https://slotsonline.fun/">cashman casino slots free</a> penny slots free online <a href="https://slotsonline.fun/">free slots slotomania</a> | https://slotsonline.fun/ - free slots online no download https://slotsonline.fun/ - free slots games online

pala casino ftelk

ignition casino <a href="https://online-casinos.icu/">vegas world casino games</a> spirit mountain casino <a href="https://online-casinos.icu/">bigfish casino online games</a> | https://online-casinos.icu/ - royal river casino https://online-casinos.icu/ - pechanga casino

empire city casino online lftyn

hollywood online casino <a href="http://online-casino.icu/">foxwoods casino</a> online casinos <a href="http://online-casino.icu/">casino slot</a> | http://online-casino.icu/ - codeshareonline doubledown casino http://online-casino.icu/ - rivers casino

ignition casino iaxbz

empire city casino <a href="https://onlinecasino888.us.org/">mountaineer casino</a> dakota sioux casino <a href="https://onlinecasino888.us.org/">snoqualmie casino</a> | https://onlinecasino888.us.org/ - 888 casino https://onlinecasino888.us.org/ - downstream casino

virgin casino online nj ceenf

pechanga casino <a href="https://casino-games.icu/">casinos near me</a> choctaw casino durant oklahoma <a href="https://casino-games.icu/">potawatomi casino</a> | https://casino-games.icu/ - jackpot party casino https://casino-games.icu/ - huuuge casino

pala casino online jbqtc

rivers casino <a href="https://casinorealmoney.fun/">sandia casino</a> parx casino <a href="https://casinorealmoney.fun/">royal river casino</a> | https://casinorealmoney.fun/ - virgin online casino https://casinorealmoney.fun/ - casino slot

casino slots cpzdk

grand falls casino <a href="https://onlinecasinowp.us.org/">three rivers casino</a> free casino <a href="https://onlinecasinowp.us.org/">caesars online casino</a> | https://onlinecasinowp.us.org/ - gsn casino https://onlinecasinowp.us.org/ - play online casino

free casino slot games jesxd

brian christopher slots <a href="https://onlineslots.tools/">online slot games</a> free online slots games <a href="https://onlineslots.tools/">free vegas slots online</a> | https://onlineslots.tools/ - free slots online no download no registration https://onlineslots.tools/ - free slot games online

slot games pvijy

casino slots <a href="https://onlineslotgames.tools/">vegas slots online</a> slot games free <a href="https://onlineslotgames.tools/">free games for casino slots hollywood</a> | https://onlineslotgames.tools/ - wizard of oz slots https://onlineslotgames.tools/ - slots online free

simslots free slots kwxok

free slot machine games <a href="http://freeslots.icu/">pop slots</a> slot games free <a href="http://freeslots.icu/">pompeii slots</a> | http://freeslots.icu/ - liberty slots casino http://freeslots.icu/ - slots games free

hollywood casino free slots online zgjhx

vegas slots free <a href="https://online-slots.fun/">slot machines</a> real casino slots <a href="https://online-slots.fun/">free slots machines</a> | https://online-slots.fun/ - online slots real money https://online-slots.fun/ - free online casino slots

free online slots gjqmz

online slots real money <a href="https://playslots.icu/">vegas world free games online slots</a> play slots <a href="https://playslots.icu/">buffalo slots</a> | https://playslots.icu/ - free slots no download https://playslots.icu/ - monopoly slots

free slot games with no download dodqf

simslots free slots <a href="https://casinoslotsparty.com/">jackpot party casino slots</a> free buffalo slots <a href="https://casinoslotsparty.com/">vegas free slots online</a> | https://casinoslotsparty.com/ - vegas world free games online slots https://casinoslotsparty.com/ - hollywood casino free slot play

play slots online lepbr

hollywood casino free slots online <a href="https://slotswall.com/">free slots games online</a> slots for free <a href="https://slotswall.com/">free slots online no download no registration</a> | https://slotswall.com/ - pompeii slots https://slotswall.com/ - heart of vegas free slots

Pages

Add new comment

Target Image