Mysql using filesort depends on LIMIT

Question

I have this query:

SELECT * 
FROM `content` AS `c` 
WHERE c.module = 20 
  AND c.categoryID 
    IN ('50', '31', '16', '10') 
ORDER BY `c`.`released` DESC 
LIMIT 5

...and I have two machines. On first machine EXPLAIN query returns 'Using where' for extra field when LIMIT is less than 3. When LIMIT is greater then 3 it returns 'Using where; Using filesort' (filesort is run for limit greater than 3).

On the other machine, it is different for the same DB and same query: when LIMIT is greater than 60 than filesort is run.

On both machines, my.cnf are the same!!!

Does anyone know how using filesort depends on LIMIT and how this dependency can be changed?

Answer 1

From MySQL LIMIT Optimization:

• If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.
• If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

---

Additionally doing a filesort or not, may also depend on indexes you have in the tables (are they same in both cases?), the number of rows in the tables (is it the same?).

One note regarding same my.cnf. This may be the same but is amount of memory the same in both machines? MySQL may not be able to use same amount of memory in both.

Answer 2

Does anyone know how using filesort depends on LIMIT and how this dependency can be changed?

When you use ORDER BY in your query, MySQL should decide how to make the output ordered.

It can either scan the table and sort the results, or scan the index on the ORDER BY column. In the latter case, no sorting required, but additional table lookup should be done for each record retrieved from the index.

Because of the table lookups, an index scan is about 10 times as expensive as a table scan.

This means that the index scan will be only preferred if less than 10% (or a little more because of the sorting overhead) of records would need to be scanned.

If you use LIMIT 3, you limit the number of records to be scanned and the index scan becomes a more preferred way.