Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.12, 10.3.4
-
None
Description
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1(id INT PRIMARY KEY, msg VARCHAR(10));
|
|
|
INSERT INTO t1 VALUES(1, 'abc1');
|
INSERT INTO t1 VALUES(2, 'abc2');
|
INSERT INTO t1 VALUES(3, 'abc3');
|
INSERT INTO t1 VALUES(4, 'abc4');
|
INSERT INTO t1 VALUES(5, 'abc5');
|
INSERT INTO t1 VALUES(6, 'abc6');
|
INSERT INTO t1 VALUES(7, 'abc7');
|
INSERT INTO t1 VALUES(8, 'abc8');
|
INSERT INTO t1 VALUES(9, 'abc9');
|
|
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2(id INT PRIMARY KEY, msg VARCHAR(10));
|
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
|
EXPLAIN SELECT t1.id FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE id=5);
|
|
|
EXPLAIN DELETE FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE id=5);
|
|
|
EXPLAIN DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id AND t2.id = 5;
|
In the SELECT case the query plan looks fine and the subquery is optimized away into a regular join:
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
| 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
In the DELETE case the subquery is still there, and a full table scan is done on table t1:
+------+--------------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
+------+--------------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
Rewriting the DELETE to use the multi table form works as a workaround:
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
Similar problems can be seen when using a secondary index in the subquery:
ALTER TABLE t2 ADD INDEX(msg);
|
|
|
MariaDB [test]> EXPLAIN SELECT t1.id FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE msg='abc5');
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
| 1 | PRIMARY | t2 | ref | PRIMARY,msg | msg | 13 | const | 1 | Using where; Using index |
|
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id | 1 | Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> EXPLAIN DELETE FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE msg='abc5');
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | unique_subquery | PRIMARY,msg | PRIMARY | 4 | func | 1 | Using where |
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> EXPLAIN DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id AND t2.msg='abc5';
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
| 1 | SIMPLE | t2 | ref | PRIMARY,msg | msg | 13 | const | 1 | Using where; Using index |
|
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id | 1 | |
|
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|
2 rows in set (0.00 sec)
|
|
See also: https://bugs.mysql.com/35794
