[SOLVED] Query optimise - tmp table on disk

[jaymc]

I have a query which is causing problems on my server. I have used pastebin to provide the information

 

http://pastebin.com/m45cba245

 

If I remove the ORDER BY p.timestamp DESC the query is practically instant

With the ORDER BY, it uses file sore and tempory table on disk. The query can take up to 60 seconds sometimes

 

The hub_posts table contains 100,000 rows

 

Any ideas how I can re write this query or maybe add an index to take the presure off when doing the ORDER

[jaymc]

Any takers?

[fenway]

Please post the query and EXPLAIN on *this* forum...

[jaymc]

#
EXPLAIN
#
id      select_type     table   type    possible_keys   key     key_len         ref     rows    Extra
#
1       SIMPLE  c       const   PRIMARY,covering        PRIMARY         27      const   1       USING temporary; USING filesort
#
1       SIMPLE  m       const   username_2      username_2      22      const   1        
#
1       SIMPLE  hc      ALL     PRIMARY         NULL    NULL    NULL    19      
#
1       SIMPLE  t       ref     PRIMARY,category_id     category_id     4       db_name.hc.id   121      
#
1       SIMPLE  p       ref     PRIMARY,category_id     category_id     4       db_name.t.id    13      USING WHERE
#
1       SIMPLE  hs      const   PRIMARY         PRIMARY         27      const   1        
#
1       SIMPLE  pt      eq_ref  PRIMARY         PRIMARY         4       db_name.p.id    1 

[corbin]

I would just add an index on timestamp.