Rechercher dans le manuel MySQL

8.2.1.18 LIMIT Query Optimization

Table des matières

If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data.

MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause:

  • If you select 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 combine LIMIT row_count with ORDER BY, MySQL stops 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 are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

    One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

  • If you combine 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 index in order (or doing a sort on the index), then calculating summaries until the index 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. In that case, the number of rows can be retrieved with SELECT FOUND_ROWS(). See Section 12.15, “Information Functions”.

  • LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns within applications that use a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types.

  • If the server uses temporary tables to resolve a query, it uses the LIMIT row_count clause to calculate how much space is required.

  • If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation.

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

  1. mysql> SELECT * FROM ratings ORDER BY category;
  2. +----+----------+--------+
  3. | id | category | rating |
  4. +----+----------+--------+
  5. |  1 |        1 |    4.5 |
  6. |  5 |        1 |    3.2 |
  7. |  3 |        2 |    3.7 |
  8. |  4 |        2 |    3.5 |
  9. |  6 |        2 |    3.5 |
  10. |  2 |        3 |    5.0 |
  11. |  7 |        3 |    2.7 |
  12. +----+----------+--------+

Including LIMIT may affect order of rows within each category value. For example, this is a valid query result:

  1. mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
  2. +----+----------+--------+
  3. | id | category | rating |
  4. +----+----------+--------+
  5. |  1 |        1 |    4.5 |
  6. |  5 |        1 |    3.2 |
  7. |  4 |        2 |    3.5 |
  8. |  3 |        2 |    3.7 |
  9. |  6 |        2 |    3.5 |
  10. +----+----------+--------+

In each case, the rows are sorted by the ORDER BY column, which is all that is required by the SQL standard.

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

  1. mysql> SELECT * FROM ratings ORDER BY category, id;
  2. +----+----------+--------+
  3. | id | category | rating |
  4. +----+----------+--------+
  5. |  1 |        1 |    4.5 |
  6. |  5 |        1 |    3.2 |
  7. |  3 |        2 |    3.7 |
  8. |  4 |        2 |    3.5 |
  9. |  6 |        2 |    3.5 |
  10. |  2 |        3 |    5.0 |
  11. |  7 |        3 |    2.7 |
  12. +----+----------+--------+
  13.  
  14. mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
  15. +----+----------+--------+
  16. | id | category | rating |
  17. +----+----------+--------+
  18. |  1 |        1 |    4.5 |
  19. |  5 |        1 |    3.2 |
  20. |  3 |        2 |    3.7 |
  21. |  4 |        2 |    3.5 |
  22. |  6 |        2 |    3.5 |
  23. +----+----------+--------+

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-limit-optimization.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut