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?