Bug #43187 SELECT ... WHERE (col1, col2) IN ((const, const)) is optimized, non-SELECT not
Submitted: 25 Feb 2009 13:45 Modified: 30 Jul 2012 17:42
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0, 5.1, 5.6 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[25 Feb 2009 13:45] Axel Schwenke
Description:
Assume this table:
CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY (c1, c2))

The following query uses the "const" access method and is very fast:
SELECT * FROM t1 WHERE (c1, c2) IN ((const, const)) 

But the same query as DELETE or UPDATE does a scan and is slow:
DELETE FROM t1 WHERE (c1, c2) IN ((const, const))
UPDATE t1 SET c1=const WHERE (c1, c2) IN ((const, const))

The same queries are executed fast if the condition is rewritten as "c1=const AND c2=const".

There is similar bug #31188 about row comparison and IN lists. This other bug works for SELECT, but requires multiple tuples in the IN list.

How to repeat:
drop table if exists t1;
create table t1 (c1 int, c2 int, primary key (c1, c2));
drop table if exists t2;
create table t2 (c1 serial);

insert into t2 values (NULL);
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
insert into t2 select NULL from t2;
-- we have now 1 Mio rows in t2

insert into t1 select 1, c1 from t2;
insert into t1 select 2, c1 from t2;
insert into t1 select 3, c1 from t2;
insert into t1 select 4, c1 from t2;
insert into t1 select 5, c1 from t2;
insert into t1 select 6, c1 from t2;
insert into t1 select 7, c1 from t2;
insert into t1 select 8, c1 from t2;
insert into t1 select 9, c1 from t2;
insert into t1 select 10, c1 from t2;
-- we have now 10 Mio rows in t1

-- this query is fast
select * from t1 where (c1, c2) IN ((3, 12345));

-- this query is slow
delete from t1 where (c1, c2) IN ((3, 12345));

-- this query is also slow
update t1 set c2=12345 where (c1, c2) IN ((3, 12346));

Suggested fix:
There seems to be a special optimization that transforms

SELECT ... WHERE (col1, col2, ...) IN ((const1, const2, ...))
to
SELECT ... WHERE col1=const1 AND col2=const2 AND ...

the same optimization should be done for non-SELECT queries
[25 Feb 2009 14:03] Axel Schwenke
This is on 6.0 and proves a table scan is used:

mysql>flush status;
mysql>select * from t1 where (c1, c2) IN ((4, 12345));
+----+-------+
| c1 | c2    |
+----+-------+
|  4 | 12345 | 
+----+-------+
1 row in set (0,00 sec)
mysql>show status like 'handler_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_key           | 1     | 
| Handler_read_rnd_next      | 0     | 
+----------------------------+-------+

mysql>flush status;
mysql>delete from t1 where (c1, c2) IN ((4, 12345));
Query OK, 1 row affected (3,72 sec)
mysql>show status like 'handler_%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_delete             | 1        | 
| Handler_read_key           | 0        | 
| Handler_read_rnd_next      | 10485761 | 
+----------------------------+----------+
[3 Mar 2009 18:18] Valeriy Kravchuk
Bug #43334 is marked as a duplicate of this one.
[14 Oct 2011 6:51] Valeriy Kravchuk
Bug #62731 was marked as a duplicate of this one.
[27 Feb 2012 9:20] Axel Schwenke
In MySQL-5.6 we can now use EXPLAIN to see the fail. Create and fill tables like before. Then run this SQL:

-- fetch an existing number (auto_inc not consecutive any more)
select @n:=c1 from t2 where c1>12345 limit 1;

-- explain and execution of select
explain select * from t1 where (c1, c2) IN ((4, @n))\G
select * from t1 where (c1, c2) IN ((4, @n));

-- explain and execution of delete
explain delete from t1 where (c1, c2) IN ((4, @n))\G
delete from t1 where (c1, c2) IN ((4, @n));

Result:

mysql>select @n:=c1 from t2 where c1>12345 limit 1;
+--------+
| @n:=c1 |
+--------+
|  16371 |
+--------+
1 row in set (0,00 sec)

mysql>explain select * from t1 where (c1, c2) IN ((4, @n))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index
1 row in set (0,01 sec)

mysql>select * from t1 where (c1, c2) IN ((4, @n));
+----+-------+
| c1 | c2    |
+----+-------+
|  4 | 16371 |
+----+-------+
1 row in set (0,01 sec)

mysql>explain delete from t1 where (c1, c2) IN ((4, @n))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10486405
        Extra: Using where
1 row in set (0,00 sec)

mysql>delete from t1 where (c1, c2) IN ((4, @n));
Query OK, 1 row affected (11,81 sec)
[30 Jul 2012 17:42] Paul Dubois
Noted in 5.6.6 changelog.

Queries that used WHERE (col1, col2) IN ((const, const)) were
optimized for SELECT, but not for DELETE or UPDATE.
[27 Sep 2012 16:59] Jon Stephens
Also fixed in trunk (currently tagged 5.7.0).