[drupal-devel] [bug] Block table: no key/index defined

m3avrck drupal-devel at drupal.org
Wed Sep 7 21:51:20 UTC 2005

Issue status update for 
Post a follow up: 

 Project:      Drupal
 Version:      cvs
 Component:    database system
 Category:     bug reports
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  m3avrck
 Updated by:   m3avrck
 Status:       patch (code needs review)

Will reroll patch tomorrow (seeing lots of updates going in right now).


Previous comments:

Wed, 20 Jul 2005 18:34:30 +0000 : m3avrck

The block table currently has no primary key or index defined. This
greatly hurts queries on the table, as the SQL server has no way to
introduce fast, index, based searching, which can be cachable. More
info: http://www.odetocode.com/Articles/237.aspx

I recommend the following change: 

PRIMARY KEY(module,delta);

That should satisfy the key requirements for the table and speed up
queries on this table, with most noticeable results on websites that
rely on lots of modules.

Fix should be backwards compatible as well.

I'd post a patch for this database change but I'm not sure the exact
procedures for posting a database change patch, not as straightforward,
so if anyone cares, that would be great. Nice little, almost free
performance benefit for the next HEAD version :-)


Sat, 23 Jul 2005 10:28:56 +0000 : djnz

Disagree. Blocks are infrequently retrieved from the table by module and
delta. In fact the only query that is run on every page is

SELECT * FROM {blocks} WHERE status = 1 AND region IN ('%s') ORDER BY
weight, module

... so status and region would be the first things to index (unless you
have a LOT of blocks active, weight and module are not going to make any
significant impact).


Mon, 25 Jul 2005 02:08:29 +0000 : m3avrck

Ok I do agree with you and maybe my post wasn't super optimized.

There should be some sort of *unique* key on the table and drumm agreed
with me in #drupal that PRIMARY KEY(module,delta) would be a great

As for the indexes, we can index the status and region since those are
fields that queries actually act upon. But we should have definetly
some sort of unique key defined as well for the best optimization,
along with an index on most queried fields.


Wed, 07 Sep 2005 14:48:56 +0000 : m3avrck

Going to bump this, I think we can get this performance fix into 4.7.

I would suggest based on comments:

PRIMARY KEY(module,delta);<br>

If anything, the PRIMARY KEY could be left off, however I see this as a
small additional performance boost... only hinderance on performace
would be on insert new blocks... which is significantly less frequent
than actually showing/displaying blocks.


Wed, 07 Sep 2005 15:23:15 +0000 : m3avrck

Attachment: http://drupal.org/files/issues/database_2.patch (2.46 KB)

Here's a patch that adds this support to the latest HEAD.

Tested on MySQL, both the update patch works and dropping/recreating
database patch works. Comments welcome on logistics of key/index, but
otherwise should be a nice little performance boost for 4.7.

More information about the drupal-devel mailing list