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 :-)

CommentFileSizeAuthor
#4 database_2.patch2.46 KBm3avrck

Comments

djnz’s picture

djnz commented

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

m3avrck’s picture

m3avrck commented

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.

m3avrck’s picture

m3avrck commented

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.

m3avrck’s picture

m3avrck commented
StatusFileSize
new database_2.patch2.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.

m3avrck’s picture

m3avrck commented
Status: Active » Needs review
m3avrck’s picture

m3avrck commented

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

Souvent22’s picture

Souvent22 commented

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.

m3avrck’s picture

m3avrck commented
Status: Needs review » Closed (duplicate)

Patch now part of this issue: http://drupal.org/node/30801

Jürgen Depicker’s picture

Jürgen Depicker commented
Version: x.y.z » 4.7.x-dev
Priority: Normal » Critical
Status: Closed (duplicate) » Needs review

In http://drupal.org/files/issues/database_2.patch , the patch for postgre has an error:

+ PRIMARY KEY (module,detla)
should be:
+ PRIMARY KEY (module,delta)

(this is still so in the new node http://drupal.org/files/issues/drupal_14.patch )

Zen’s picture

Zen commented
Status: Needs review » Closed (duplicate)