[development] MySql Performance Problem

[larry at garfieldtech.com]

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 at gmail.com>
> *To:* development at 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