More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How MySQL Optimizes LIMIT
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a
full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting
the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries
until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result
columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.
(Continued on next question...)
