In some cases, MySQL handles a query differently when you are
and not using
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
ORDER BY, MySQL ends the sorting as soon as it has found the first
row_countrows 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
LIMITclause must be selected, and most or all of them must be sorted, before it can be ascertained that the first
row_countrows 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
DISTINCT, MySQL stops as soon as it finds
In some cases, a
GROUP BYcan 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,
LIMITdoes not calculate any unnecessary
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.13, “Information Functions”.
LIMIT 0quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. This technique does not work with the mysql client program, which merely displays
Empty setin such cases. Instead, use
DESCRIBEfor this purpose.
When the server uses temporary tables to resolve the query, it uses the
LIMITclause to calculate how much space is required.