[development] MySql Performance Problem

[nan wich]

I suppose this is my fault for packing so much into a single query, but I really 
do want to use tablesort on it.

Here's the query (pager_query with a limit of 20)
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 realname r ON r.uid=n.uid 
  INNER JOIN radioactivity ra ON ra.id=n.nid 
  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' 
  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

On my test site, this runs really fast - but there are only 6 rows. On the 
production site, there are 8,000 nodes and this query takes 35 seconds to run.

EXPLAIN tells me:
id 
select_type 
table 
type 
possible_keys 
key 
key_len 
ref 
rows 
Extra 
1 SIMPLE ra ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 
1 SIMPLE n eq_ref PRIMARY,node_status_type,node_type,uid PRIMARY 4 
drupal6b.ra.id 1 Using where 

1 SIMPLE nc eq_ref PRIMARY PRIMARY 4 drupal6b.n.nid 1   
1 SIMPLE cs eq_ref PRIMARY PRIMARY 4 drupal6b.ra.id 1   
1 SIMPLE v ALL NULL NULL NULL NULL 3729   
1 SIMPLE ppc ALL PRIMARY NULL NULL NULL 2738   
1 SIMPLE pmc ALL PRIMARY NULL NULL NULL 2745   
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 drupal6b.n.uid 1 Using where 

With the performance I see, I am not suprised to see "filesort" in there.

Can someone suggest ways to improve the performance?


Nancy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100719/fef5dd57/attachment.html