MySQL Bugs: #43187: SELECT ... WHERE (col1, col2) IN ((const, const)) is optimized, non-SELECT not

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

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

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

Bug #43334 is marked as a duplicate of this one.

Bug #62731 was marked as a duplicate of this one.

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)

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.

Also fixed in trunk (currently tagged 5.7.0).