Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2(EOL), 11.4, 11.5(EOL), 11.6(EOL)
-
None
Description
Prepared statement shows query execution plan inconsistency issue when executing a query
The prepare statement shows a slightly different query execution plan compared to normal query execution after removing some records from a table.
When executing a query through the prepare statement, explain plan is not showing using index for join operation in the Extra information column. But in normal query execution, explain plan is showing using index information in Extra column.
SQL log
10.5.27-opt>INSERT INTO t1 VALUES (1); |
Query OK, 1 row affected (0.000 sec)
|
|
|
10.5.27-opt>INSERT INTO t2 VALUES (1),(2),(3); |
Query OK, 3 rows affected (0.000 sec) |
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
10.5.27-opt>
|
10.5.27-opt>EXECUTE stmt; |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | |
| 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | Using index | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
2 rows in set (0.001 sec) |
|
|
10.5.27-opt>EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1; |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | |
| 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | Using index | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
2 rows in set (0.000 sec) |
|
|
10.5.27-opt>
|
10.5.27-opt>DELETE FROM t2 WHERE c1 > 3; |
Query OK, 0 rows affected (0.000 sec) |
|
|
10.5.27-opt>
|
10.5.27-opt>EXECUTE stmt; |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ |
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | |
| 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | |
|
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ |
2 rows in set (0.000 sec) |
|
|
10.5.27-opt>EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1; |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | |
| 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | Using index | |
+------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ |
2 rows in set (0.000 sec) |
|
|
10.5.27-opt>
|
Test case
CREATE TABLE t1 (c1 INT, KEY(c1) ) ENGINE=MYISAM; |
CREATE TABLE t2 (c1 INT, KEY(c1) ) ENGINE=MYISAM; |
|
|
PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1'; |
|
|
|
|
INSERT INTO t1 VALUES (1); |
INSERT INTO t2 VALUES (1),(2),(3),(4),(5); |
|
|
EXECUTE stmt; |
EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1; |
|
|
DELETE FROM t2 WHERE c1 > 3; |
|
|
EXECUTE stmt; |
EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1; |
|
|
DEALLOCATE PREPARE stmt; |
DROP TABLE t1,t2; |
Attached optimize trace