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:
Comments
gsn casino games pwgwa
harrah online casino oqfmv
online casino games gqorr
mgm online casino duxfn
free online slots vegas world ekzxo
free slots with no download or registration rovwg
casino games saavy
hollywood casino free slot play zysci
casino games slots free zfupc
caesars online casino ghzhw
parx casino gcdpz
virgin casino online nj cykuh
downstream casino izurm
free online slots no download eoubf
naigevqa
play online casino bwoad
online casino slots ullkv
gsn casino games nsmlg
downstream casino wwzvo
slots online yzmdy
high 5 casino wcjwn
slots online csodb
foxwoods online casino pcuzc
real money casino lpzrl
goldfish slots psaoy
vegas world free slots hcvyq
jackpot party casino nccnu
hollywood casino fprcf
morongo casino sfzty
doubledown casino omnrt
house of fun free slots lzqsn
real money casino veaig
casino online utulm
online casino games free wpngp
caesars free slots online itbgy
casino online slots bzrjq
casino online slots jvxay
freeslots.com slots epclo
casino play rhobs
mgm online casino pyffr
vegas world slots odtpw
zone online casino games iachv
free vegas slots online kkoxo
free slots izhij
empire city casino mvxro
seneca allegany casino cbefj
free slots online no download siadp
free vegas slots online ukcmw
casino online slots pmxxw
play slots ldngt
Pages
Add new comment