Posted by robertdouglass on November 21, 2006 at 10:14pm
According to my devel module logs and lots of watching the query lists, the query that costs us the most in all of Drupal core is this one:
SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = S AND b.status = D
Let the brainstorming begin; what can we do to improve it? Are there enough indexes? Can we avoid the DISTINCT? Are there other options?
Comments
I looked at it today, and on
I looked at it today, and on a new cvs install with all blocks enabled and some custom block_role visibility settings, its a very fast (< 2ms) query. What is your particular setup (and could I get a sql dump of block and block_role?)? I'm sure it can be optimized, but to me it looks like a relatively fast query on small tables (you wont have thousands of blocks). At worse, mysql query cache will take care of it.
Post the data you have if you want (average time per query, stuff like that), I'll look at it.
Great, it ate my previous
Great, it ate my previous answer. I looked at it today, and on a new cvs install with all blocks enabled and some custom block_role visibility settings, its a very fast ( ~2ms ). What kind of site did you test on? I guess it could use some optimization, but it'll be on very small tables (you dont have thousands of blocks) - index wont help much, but ill give it a try if you have sql dumps of block and block_role that display the problem. Mysql query cache will probably take care of any bad performance this query might be displaying, as it looks pretty static (no uid, etc).
Try to catch me on irc, we can talk about it. Also, what were your average time for this query?
(I tested with "SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (2) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module". Devel module eats variables, please post the complete query you're having problems with if you can :))
5.0
blocks_roles is a 5.0 thing, right?
i am also surprised this would be slow. very little data in blocks or blocks_roles.
Slow relative to the other queries
I'm using the latest 5.0 dev version, and the only module I have turned on (beyond the ones in a default install) is path. After doing things like creating nodes, deleting nodes, users, categories, and whatnot, the devel module tells me that this is the slowest query and the one consuming the most resources.
I also get times that seem to hover between 2.8 ms and 3.5 ms. However, all the other queries on those page loads are between 0.15 and 0.3.
Note: I just retested with a larger test db, 100,000 nodes: now some of the node loading queries are significantly slower, so the title "most expensive core query" is clearly dependent on which parts of core you're using and the size of your database.
However!!! Is there no way to optimize the block and block_roles tables? No keys whatsoever makes me wonder.
node_load()
node_load() is slowest on this groups site.
MySQL explain
Have you run a MySQL explain on this query yet?
To seek, to strive, to find, and not to yield
< a href="http://www.youtube.com/watch?v=COg-orloxlY">Support the Drupal installer, Install profiles, and module install forms
<a href="http://ia310107.us.archive.org/1/items/organicgroups_og2list/dru
Who's Online
The "Who's Online" block was the biggest problem on Wikimedia's Fundraising CORE (http://fundraising.wikimedia.org/). I had to shut it off to handle the load of 1200 concurrent users.
I talked to Domas, one of the MySQL developers who works with Wikimedia. He said it was because it performs a full table scan to satisfy the query. We could add an index to the sessions table, but it would dramatically slow down table updates.
It seems like the real solution is to cache the block. I haven't tried the block cache module.
patch to cache query
I've posted a patch to address the problem you ran into. Please test and leave feedback in the issue.
According to the drupal.org
According to the drupal.org slow-query log the "slowest" query is probably the node_load query. "Slowest" in the sense that the query itself is not particularly fast and it is executed very often. Hence tuning it (or probably caching it) will bring good results.