10.7 How MySQL optimizes LIMIT

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

• If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
• If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
• When combinating LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
• In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP's.
• As soon as MySQL has sent the first # rows to the client, it will abort the query.
• LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
• The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.