(mysql.info.gz) LIMIT optimization

Info Catalog

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) How to avoid table scan

 
 7.2.12 How MySQL Optimizes `LIMIT'
 ----------------------------------
 
 In some cases, MySQL will handle 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 lines rather
      than sorting the whole table.
 
    * 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' will
      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' always quickly returns an empty set.  This is useful to
      check the query or to get the column types of the result columns.
 
    * When the server uses temporary tables to resolve the query, the
      `LIMIT ROW_COUNT' is used to calculate how much space is required.
 

Info Catalog

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) How to avoid table scan