Re: ORDER BY Optimization depends on LIMIT

[Rick James]

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).