If you need only a specified number of rows from a result set,
LIMIT clause in the query, rather
than fetching the whole result set and throwing away the extra
MySQL sometimes optimizes a query that has a
clause and no
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 use
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
LIMIT clause are selected,
and most or all of them are sorted, before the first
row_count are found. After the
initial rows have been found, MySQL does not sort any
remainder of the result set.
DISTINCT, MySQL stops as soon as it
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,
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
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 (the
mysql program), which merely displays
Empty set in such cases; instead, use
SHOW COLUMNS or
DESCRIBE for this purpose.)
When the server uses temporary tables to resolve the
query, it uses the
calculate how much space is required.