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 <[email protected]>
*To:* [email protected]
*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
