[#MDEV-15385] Full scan instead of index lookup on single table DELETE ... WHERE IN (SELECT ...)

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 |

+------+--------------------+-------+-------+---------------+---------+---------+-------+------+-------------+

+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| 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 |

+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

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)

[MDEV-15385] Full scan instead of index lookup on single table DELETE ... WHERE IN (SELECT ...) Created: 2018-02-22 Updated: 2023-10-31 Resolved: 2023-10-24
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Delete, Optimizer
Affects Version/s:	10.2.12, 10.3.4
Fix Version/s:	11.1.0