Jump to content

[SOLVED] Query optimise - tmp table on disk


jaymc

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/130125-solved-query-optimise-tmp-table-on-disk/
Share on other sites

#
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 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.