(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