Optimize Similar By Terms core query

Hi,

I found that the core query consumes quite some mysql resources and is constantly coming up in my slow query log. Enabling block cache does help, still I think we could try optimizing it further.

Here is the query from the slow queries log:

SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM drupal_node n INNER JOIN drupal_term_node tn ON n.nid = tn.nid WHERE tn.tid IN (6619731,100000003,100045073,100040326,100375086,100375087,100049744,100375088,100008256,100118281,100002872) AND n.nid != 370972 AND n.status = 1 AND n.moderate = 0 GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 9;

Results from explain

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	tn 	range 	PRIMARY,nid,tid 	PRIMARY 	4 	NULL 	49187 	Using where; Using index; Using temporary; Using filesort
1 	SIMPLE 	n 	ref 	PRIMARY,status,node_moderate,node_status_type,nid 	nid 	4 	db53452_clipglobe.tn.nid 	1 	Using where

As I understand using COUNT - GROUP BY with a JOIN and ORDER BY can be a killer.

I found some tips on optimizing it, but have not yet achieved great success. If anyone has ideas please share:

Here are a few articles I found so far and trying to work my way through them:
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-opt...
http://www.mysqlperformanceblog.com/2008/09/20/a-common-problem-when-opt...
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performanc...