MySQL Forums
Forum List  »  Optimizer & Parser

Re: avoid filesort
Posted by: Rick James
Date: January 24, 2012 01:17AM

SELECT  f_thread.id,f_thread.subject, 
      ( SELECT  MAX(postdate)
            FROM  f_post
            WHERE  f_post.thread_id = f_thread.id
            LIMIT  1          -- redundant, see note
      ) AS last_post
    FROM  f_thread
    ORDER BY  last_post DESC  -- must gather all rows before sorting
    LIMIT  25                 -- cannot do this until after the sort

The LIMIT 1 is redundant with the MAX; you can remove the LIMIT.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Given your schema and your desired query, the "filesort" is unavoidable.

The "filesort" is not the villain, it the fact that it needs to fetch the last_post for _every_ thread before it can sort (ORDER BY) them, and finally deliver a few (25) of them.

I assume f_post has an index starting with thread_id. If not, that is a significant flaw.

Options: ReplyQuote


Subject
Views
Written By
Posted
1620
January 22, 2012 01:08PM
Re: avoid filesort
998
January 24, 2012 01:17AM
1088
January 24, 2012 04:36PM
1185
January 25, 2012 07:24PM
1025
January 26, 2012 04:57AM
1023
January 25, 2012 08:26PM
997
January 26, 2012 04:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.