[development] MySql Performance Problem

[nan wich]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100719/79dc9099/attachment-0001.html