Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15385

Full scan instead of index lookup on single table DELETE ... WHERE IN (SELECT ...)

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                hholzgra Hartmut Holzgraefe
              • Votes:
                4 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: