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.