An efficient plan to execute a query is changed for a full scan plan after the first execution of PS
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
In the following scenario a full scan plan to execute a single-table query is chosen for a non-first execution
of the prepared statement created for this query:
1. create a prepared statement for a query that requires a simple look-up with a primary key
2. execute the prepared statement one or more times
3. execute a query over the same table ignoring all indexes
4. execute the prepared statement again
You'll see that the last execution uses a full scan.
The problem exists for mariadb-5.3 code line and can be demonstrated with the following test case:
create table t1 (a int primary key, b int);
insert into t1 values
(7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70);
prepare st from 'select * from t1 where a=8';
flush status;
execute st;
show status like '%Handler_read%';
flush status;
execute st;
show status like '%Handler_read%';
flush status;
select * from t1 use index() where a=3;
show status like '%Handler_read%';
flush status;
execute st;
show status like '%Handler_read%';
deallocate prepare st;
drop table t1;
You can see the problem from the following output when running this test case:
MariaDB [test]> prepare st from 'select * from t1 where a=8';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
MariaDB [test]>
MariaDB [test]> flush status;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> execute st;
+---+------+
| a | b |
+---+------+
| 8 | 70 |
+---+------+
1 row in set (0.00 sec)
MariaDB [test]> show status like '%Handler_read%';
+------
| Variable_name | Value |
+------
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
+------
6 rows in set (0.00 sec)
MariaDB [test]> flush status;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> execute st;
+---+------+
| a | b |
+---+------+
| 8 | 70 |
+---+------+
1 row in set (0.00 sec)
MariaDB [test]> show status like '%Handler_read%';
+------
| Variable_name | Value |
+------
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
+------
6 rows in set (0.00 sec)
MariaDB [test]> flush status;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from t1 use index() where a=3;
+---+------+
| a | b |
+---+------+
| 3 | 40 |
+---+------+
1 row in set (0.00 sec)
MariaDB [test]> show status like '%Handler_read%';
+------
| Variable_name | Value |
+------
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
+------
6 rows in set (0.00 sec)
MariaDB [test]> flush status;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> execute st;
+---+------+
| a | b |
+---+------+
| 8 | 70 |
+---+------+
1 row in set (0.00 sec)
MariaDB [test]> show status like '%Handler_read%';
+------
| Variable_name | Value |
+------
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
+------
6 rows in set (0.01 sec)
Changed in maria: | |
importance: | Undecided → Critical |
status: | New → Confirmed |
assignee: | nobody → Igor Babaev (igorb-seattle) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |