MySQL Forums
Forum List  »  Optimizer & Parser

Re: ORDER BY Optimization depends on LIMIT
Posted by: Rick James
Date: March 25, 2011 08:03AM

That's a new one on me.

You could play around with the "USE INDEX FOR ..." syntax in
http://dev.mysql.com/doc/refman/5.1/en/join.html
But I don't expect much.

SELECT  *
    FROM  `content` AS `c`
    WHERE  c.module = 20
      AND  c.categoryID IN ('50', '31', '16', '10')
    ORDER BY  `c`.`released` DESC
    LIMIT  5
begs for this compound key, preferably in this order:
INDEX(module, categoryID)
Do you have such?

In general, it switches to filesort when it realizes that filesort is more efficient. Also, "filesort" does not necessarily imply that it even touches the disk.

Could you do the EXPLAIN for both cases? I wonder if it says something different.

In _some_ circumstances, this could perform better:
SELECT  c.*
    FROM  `content` AS `c`
    JOIN  (
        SELECT id
            FROM  `content`
            WHERE  module = 20
              AND  categoryID IN ('50', '31', '16', '10')
            ORDER BY  `released` DESC
            LIMIT  5
          ) AS x  ON x.id = c.id; 
    ORDER BY  `released` DESC  -- (yes, again)
However, that begs for
INDEX(module, categoryID, released, id)
and assumes the PRIMARY KEY is (id).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: ORDER BY Optimization depends on LIMIT
1154
March 25, 2011 08:03AM


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.