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