Optimizing LIMIT Queries

In some cases, MySQL handles a query differently when you are using LIMIT row_count and not using HAVING:

  • If you select 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 combine 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.

  • If you combine LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

  • In some cases, a GROUP BY can be resolved by reading the index in order (or doing a sort on the index) and then calculating summaries until the index value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

  • As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 12.14, “Information Functions”.

  • LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns if you are using a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types.

  • If the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.