Rechercher dans le manuel MySQL
8.2.1.18 LIMIT Query Optimization
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
clause and no
row_count
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
withrow_count
ORDER BY
, MySQL stops sorting as soon as it has found the firstrow_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 theLIMIT
clause are selected, and most or all of them are sorted, before the firstrow_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 withoutLIMIT
may return rows in different order, as described later in this section.If you combine
LIMIT
withrow_count
DISTINCT
, MySQL stops as soon as it findsrow_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
does not calculate any unnecessaryrow_count
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 withSELECT 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
clause to calculate how much space is required.row_count
If an index is not used for
ORDER BY
but aLIMIT
clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memoryfilesort
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:
- +----+----------+--------+
- | id | category | rating |
- +----+----------+--------+
- | 1 | 1 | 4.5 |
- | 5 | 1 | 3.2 |
- | 3 | 2 | 3.7 |
- | 4 | 2 | 3.5 |
- | 6 | 2 | 3.5 |
- | 2 | 3 | 5.0 |
- | 7 | 3 | 2.7 |
- +----+----------+--------+
Including LIMIT
may affect order of rows
within each category
value. For example,
this is a valid query result:
- +----+----------+--------+
- | id | category | rating |
- +----+----------+--------+
- | 1 | 1 | 4.5 |
- | 5 | 1 | 3.2 |
- | 4 | 2 | 3.5 |
- | 3 | 2 | 3.7 |
- | 6 | 2 | 3.5 |
- +----+----------+--------+
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:
- +----+----------+--------+
- | id | category | rating |
- +----+----------+--------+
- | 1 | 1 | 4.5 |
- | 5 | 1 | 3.2 |
- | 3 | 2 | 3.7 |
- | 4 | 2 | 3.5 |
- | 6 | 2 | 3.5 |
- | 2 | 3 | 5.0 |
- | 7 | 3 | 2.7 |
- +----+----------+--------+
- +----+----------+--------+
- | id | category | rating |
- +----+----------+--------+
- | 1 | 1 | 4.5 |
- | 5 | 1 | 3.2 |
- | 3 | 2 | 3.7 |
- | 4 | 2 | 3.5 |
- | 6 | 2 | 3.5 |
- +----+----------+--------+
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-limit-optimization.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.