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

[Souvent22]

Issue status update for 
http://drupal.org/node/27157
Post a follow up: 
http://drupal.org/project/comments/add/27157

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

I agree taht some sort of indexing needs to be done on the blocks.
Although, on "most" sites, this is not a problem. But, with any scale
(be it many many blocks, or many many hits), the blocks will be
retrived either  often (site with many hits) or become a large query
(many blocks, however if you have that many blocks, you may want to
look at your site design). Either way, this would make for a
performance improvement. I know when optimizing a site, especially one
that recieves many hits; it's hard to find just one improvement, it's
usually the little tweaks hear and there that result in an overall
large perfomance gain.




Souvent22



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
candidate.


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>
INDEX(status,region);


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.




------------------------------------------------------------------------

Wed, 07 Sep 2005 21:51:13 +0000 : m3avrck

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