DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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
automatically generated byinfo2html