(mysql.info) limit-optimization
Info Catalog
(mysql.info) group-by-optimization
(mysql.info) query-speed
(mysql.info) how-to-avoid-table-scan
7.2.14 `LIMIT' Optimization
---------------------------
In some cases, MySQL handles a query differently when you are using
`LIMIT ROW_COUNT' and not using `HAVING':
* If you are selecting 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 use `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.
* When combining `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 key in
order (or doing a sort on the key) and then calculating summaries
until the key 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'.
* `LIMIT 0' quickly 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 trick does not work in the MySQL Monitor,
which merely displays `Empty set' in such cases; you should
instead use `SHOW COLUMNS' or `DESCRIBE' for this purpose.)
* When the server uses temporary tables to resolve the query, it
uses the `LIMIT ROW_COUNT' clause to calculate how much space is
required.
Info Catalog
(mysql.info) group-by-optimization
(mysql.info) query-speed
(mysql.info) how-to-avoid-table-scan
automatically generated byinfo2html