In some cases, MySQL handles a query differently when you are
and not using
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
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
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.
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
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; you should
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.