How would you optimize the following query?

I am using the following query to find out up to 6 pages viewed on my Drupal site:

SELECT n.title, n.nid, c.daycount 
FROM node n 
JOIN node_counter c ON n.nid=c.nid 
WHERE n.type='page' AND n.status = 1 
ORDER BY c.daycount DESC
LIMIT 0,6;

      

        
        
        
      

    

This is very natural and works well on most sites. However, on a site with many nodes (1.7m), it comes out rather slowly and is unlikely to be cached as the node table keeps changing as users add / edit nodes on the system.

Running the explanation on a heavy site gives the following output:

+----+-------------+-------+--------+-----------------------------------------------+------------------+---------+------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                 | key              | key_len | ref              | rows  | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------+------------------+---------+------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | n     | ref    | PRIMARY,node_type,status,node_status_type,nid | node_status_type | 102     | const,const      | 71878 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                                       | PRIMARY          | 4       | kidstvprd2.n.nid |     1 | Using where                                  |
+----+-------------+-------+--------+-----------------------------------------------+------------------+---------+------------------+-------+----------------------------------------------+

      

        
        
        
      

    

Note the "Using where; Using a temporary file using filesort".

One solution I was thinking of is running this query offline (probably in cron) and saving the results in another table for those reading, until the next cron update. However, before returning to cron, I would like to try and optimize this query.

Does anyone have an idea on how to optimize it?

thanks