|
Here is a test case that fails without restarting the server
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(1));
|
INSERT INTO t1 VALUES (262,'8'), (179, '9');
|
|
SELECT 1 FROM t1
|
WHERE (1 IN (SELECT 8 UNION SELECT 5)) OR t1.a = 140
|
LIMIT ROWS EXAMINED 1;
|
|
|
The query after restart fails because we try to read_statistics_for_the_table and then looking up in the stat tables via index increase the value for the THD::accessed_rows_and_keys
|
|
Patch
http://lists.askmonty.org/pipermail/commits/2020-April/014246.html
|
|
Here are some dataset to demonstrate how LIMIT ROWS EXAMINED is used
CREATE TABLE t1 (a int PRIMARY KEY, b INT);
|
INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
|
|
CREATE TABLE t2 (a int, b INT);
|
INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
|
|
CREATE TABLE t3(a INT PRIMARY KEY);
|
INSERT INTO TABLE t3 VALUES (1);
|
MariaDB [test]> select count(*) FROM t1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 100 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select count(*) FROM t2;
|
+----------+
|
| count(*) |
|
+----------+
|
| 100 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select * from t3;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
Case 1: With a subquery (single row result)
MariaDB [test]> SELECT * FROM t1 WHERE b = (SELECT MIN(a) from t2) LIMIT ROWS EXAMINED 1;
|
Empty set, 1 warning (0.00 sec)
|
|
MariaDB [test]> show warnings;
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 1931 | Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete. |
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Here the subquery is executed at the execution layer. So the LIMIT ROWS EXAMINED is taken into account for the subquery execution also
MariaDB [test]> SELECT * FROM t1 WHERE a = (SELECT MIN(a) from t2) LIMIT ROWS EXAMINED 1;
|
+---+------+
|
| a | b |
|
+---+------+
|
| 1 | 1 |
|
+---+------+
|
1 row in set (0.00 sec)
|
Here the subquery is executed at the optimization phase and we do not consider the LIMIT ROWS EXAMINED for the subquery.
|
Case 2 With Union
The query is:
(SELECT * FROM t1) UNION (SELECT * FROM t2) LIMIT ROWS EXAMINED 5;
|
MariaDB [test]> (SELECT * FROM t1) UNION (SELECT * FROM t2) LIMIT ROWS EXAMINED 5;
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 1 |
|
| 2 | 2 |
|
| 3 | 3 |
|
+------+------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
MariaDB [test]> show warnings;
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 1931 | Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete. |
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
The query shows that it examined 7 rows, so it did not even execute the entire first select of the UNION.
|
|
So for a UNION the execution is as follows:
Execute individual selects and apply the LIMIT ROWS EXAMINED and stop when the limit is reached. Then we execute the fake select for the result of UNION, limit rows examined is not considered here.
The code snippet regarding this (inside the function st_select_lex_unit::exec)
/*
|
Disable LIMIT ROWS EXAMINED in order to produce the possibly incomplete
|
result of the UNION without interruption due to exceeding the limit.
|
*/
|
thd->lex->limit_rows_examined_cnt= ULONGLONG_MAX;
|
|
|
|
Case 3: Reading const table at optimization phase
Query:
SELECT * FROM t2, t3 A, t3 B WHERE (A.a = 2 OR B.a = 10 OR t2.a >= 1);
|
t3 is a const table
ariaDB [test]> EXPLAIN SELECT * FROM t2, t3 A, t3 B WHERE (A.a = 2 OR B.a = 10 OR t2.a >= 1) LIMIT ROWS EXAMINED 2;
|
+------+-------------+-------+--------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | A | system | PRIMARY | NULL | NULL | NULL | 1 | |
|
| 1 | SIMPLE | B | system | PRIMARY | NULL | NULL | NULL | 1 | |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
+------+-------------+-------+--------+---------------+------+---------+------+------+-------------+
|
3 rows in set (0.00 sec)
|
This is the plan, that is we read 2 rows at the optimization phase and LIMIT ROWS EXAMINED is not considered.
When it is set at the execution layer then the execution is terminated.
|
|
Also found a comment regarding enabling LIMIT ROWS EXAMINED at the execution phase:
In the codebase (currently looking at 10.3), it is inside the function JOIN::exec_inner
/*
|
Enable LIMIT ROWS EXAMINED during query execution if:
|
(1) This JOIN is the outermost query (not a subquery or derived table)
|
This ensures that the limit is enabled when actual execution begins, and
|
not if a subquery is evaluated during optimization of the outer query.
|
(2) This JOIN is not the result of a UNION. In this case do not apply the
|
limit in order to produce the partial query result stored in the
|
UNION temp table.
|
*/
|
if (!select_lex->outer_select() && // (1)
|
select_lex != select_lex->master_unit()->fake_select_lex) // (2)
|
thd->lex->set_limit_rows_examined();
|
|