GNU Info

(mysql.info)LIMIT optimization

---

Next: Insert speed Prev: LEFT JOIN optimization Up: Query Speed

---

Enter node , (file) or (file)node

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.