The query is doing a WHERE on the node table and an ORDER BY on the radioactivity table. That will force a temp table no matter what you do. If the temp table is larger than some size (no idea how MySQL determines that size) it will dump it to disk, hence the filesort. Your best bet is to try and reduce the size of the result set as much as possible to keep the temp table small. That is, if you can throw anything else into the WHERE clause on the node table (eg, node type, created recently, etc.) that will probably help. Indexing the votingapi fields you're joining on may help. Don't worry about the write cost. Updating an index on an integer field is pretty fast, and if you're then joining on that field it's going to be a net win in most cases. Try it and see how much difference it makes. --Larry Garfield On 7/19/10 1:11 PM, nan wich wrote:
Print_page_counter and print_mail_page_counter both have indexes on "path" which is how I am accessing them. I added "AND v.content_type='node'" to the votingapi_cache because it has an index on content_type and content_id. That cut the time in half. Radioactivity has an index on id, class, and decay_profile (in that order). My query has id and class; but does not have decay_profile. Thank you, it is faster now, but I'd still like it even faster. So I now have SELECT n.nid, n.title, n.status, n.created, n.uid, ra.energy, nc.totalcount, nc.daycount, ppc.totalcount AS printcount, pmc.sentcount, r.realname, cs.comment_count, v.value AS votes FROM node n INNER JOIN radioactivity ra ON ra.id=n.nid AND ra.class='node' INNER JOIN realname r ON r.uid=n.uid LEFT JOIN node_counter nc ON nc.nid=n.nid LEFT JOIN node_comment_statistics cs ON cs.nid=n.nid LEFT JOIN votingapi_cache v ON v.content_id=n.nid AND v.function='sum' AND v.content_type='node' LEFT JOIN print_page_counter ppc ON ppc.path=CONCAT('node/', n.nid) LEFT JOIN print_mail_page_counter pmc ON pmc.path=CONCAT('node/', n.nid) WHERE n.type = 'blog' AND n.status = 1 ORDER BY ra.energy DESC LIMIT 10
/*Nancy*/
------------------------------------------------------------------------ *From:* Domenic Santangelo <domenics@gmail.com> *To:* development@drupal.org *Sent:* Mon, July 19, 2010 1:55:48 PM *Subject:* Re: [development] MySql Performance Problem
On Jul 19, 2010, at 10:18 AM, nan wich wrote:
Can someone suggest ways to improve the performance?
First thing to do: indexes on the radioactivity, print_page_counter, print_mail_page_counter and (possibly) votingapi_cache tables. I say possibly on that one because I can't remember the behavior of that table and if indexing it might decrease performance elsewhere.
hth, D