LIMIT ROWS EXAMINED
SELECT ... FROM ... WHERE ... [group_clause] [order_clause] LIMIT [[offset,] row_count] ROWS EXAMINED rows_limit;
Similar to the parameters of
rows_limit can be both a
prepared statement parameter, or a stored program parameter.
The purpose of this optimization is to provide the means to terminate the
SELECT statements which examine too many rows, and
thus use too many resources. This is achieved through an extension of the
LIMIT clause —
LIMIT ROWS EXAMINED number_of_rows . Whenever possible the
LIMIT ROWS EXAMINED is the same as that of normal
(for instance for aggregate functions).
LIMIT ROWS EXAMINED clause is taken into account by the query engine
only during query execution. Thus the clause is ignored in the following cases:
- If a query is
- During query optimization.
- During auxiliary operations such as writing to system tables (e.g. logs).
The effects of this clause are as follows:
- The server counts the number of read, inserted, modified, and deleted rows during query execution. This takes into account the use of temporary tables, and sorting for intermediate query operations.
- Once the counter exceeds the value specified in the LIMIT ROWS EXAMINED clause, query execution is terminated as soon as possible.
- The effects of terminating the query because of LIMIT ROWS EXAMINED are as
- The result of the query is a subset of the complete query, depending on when the query engine detected that the limit was reached. The result may be empty if no result rows could be computed before reaching the limit.
- A warning is generated of the form: "Query execution was interrupted. The query examined at least 100 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete."
- If query processing was interrupted during filesort, an error is returned in addition to the warning.
- If a UNION was interrupted during execution of one of its queries, the last step of the UNION is still executed in order to produce a partial result.
- Depending on the join and other execution strategies used for a query, the same query may produce no result at all, or a different subset of the complete result when terminated due to LIMIT ROWS EXAMINED.
- If the query contains a GROUP BY clause, the last group where the limit was reached will be discarded.
LIMIT ROWS EXAMINED clause cannot be specified on a per-subquery basis.
There can be only one
LIMIT ROWS EXAMINED clause for the whole
statement. If a
SELECT statement contains several subqueries
LIMIT ROWS EXAMINED, the one that is parsed last is taken into
A simple example of the clause is:
SELECT * from t1, t2 LIMIT 10 ROWS EXAMINED 10000;
LIMIT ROWS EXAMINED clause is global for the whole statement.
If a composite query (such as
UNION, or query with derived tables or with
subqueries) contains more than one
LIMIT ROWS EXAMINED, the last one parsed
is taken into account. In this manner either the last or the outermost one is
taken into account. For instance, in the query:
SELECT * FROM t1 WHERE c1 IN (SELECT * FROM t2 WHERE c2 > ' ' LIMIT ROWS EXAMINED 0) LIMIT ROWS EXAMINED 11;
The limit that is taken into account is 11, not 0.