How does MySQL execute an aggregate query with a limit?

Question

Does it group all possible results and then send back the results found within the given LIMIT?

Answer 1

The LIMIT clause is only selection of the records returned - it has nothing to do with the value(s) returned from the query itself. For example, if you use LIMIT 10 on a query that only returns 5 rows - you'll only get 5 rows. If the query returned 11+ rows, you'd only get 10 rows. For a query using LIMIT to consistently return the same results, you need to specify an ORDER BY clause.

Think of the query occurring in the following steps:

1. Query without ORDER BY or LIMIT
2. ORDER BY criteria applied to query output from Step 1
3. LIMIT criteria applied to output from Steps 1 & 2

If there's no ORDER BY specified, the step is not performed.

Answer 2

This page from the MySQL manual explains the ways in which it optimizes queries that use LIMIT:

http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html

In short, it doesn't just do the naive thing, which would be to compute the entire result set and then send you the first N rows.

There are some things that will prevent optimizations, including using a HAVING clause and using SQL_CALC_FOUND_ROWS.