MySQL Bugs: #43219: delete does not use index when using OR in where clause

Bug #43219	delete does not use index when using OR in where clause
Submitted:	26 Feb 2009 9:49	Modified:	26 Feb 2009 11:38
Reporter:	Tilo Prütz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.70, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Linux (Gentoo)
Assigned to:		CPU Architecture:	Any

Description:
Given a table with two integer columns, one is the primary key both are not null and one index over both columns. The value range of the second column is 0 through 9.
The table contains 5 million rows. 500,000 for each value of the second column.
Then the following DELETE statement (affecting 500,001 rows) seems not to use the index:

DELETE FROM table WHERE col2=5 OR (col2=3 AND pk_col=123456)

The statement took over 40 seconds on my core2 duo macbookpro.
A select with the same WHERE clause takes only 8 seconds (2 on subsequent calls).

EXPLAIN says for the select: "Using where; Using index"

The DELETE statement tooks about 20 seconds even if it does nothing (10 seconds on subsequent calls).

How to repeat:
CREATE TABLE test (id int not null, type int not null) primary key id;
CREATE INDEX idx ON test (id, type);

insert rows id: 1..5000000, type 0..9

DELETE FROM test WHERE type=5 OR (type=3 AND id=123456)

Thank you for the report.

Please provide output of SHOW STATUS LIKE '%handler%'; before and after running DELETE statement.

Hi, here's the requested output.

First for DELETE which affects zero rows.

mysql> SHOW STATUS LIKE '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     | 
| Handler_delete             | 0     | 
| Handler_discover           | 0     | 
| Handler_prepare            | 0     | 
| Handler_read_first         | 0     | 
| Handler_read_key           | 0     | 
| Handler_read_next          | 0     | 
| Handler_read_prev          | 0     | 
| Handler_read_rnd           | 0     | 
| Handler_read_rnd_next      | 48    | 
| Handler_rollback           | 0     | 
| Handler_savepoint          | 0     | 
| Handler_savepoint_rollback | 0     | 
| Handler_update             | 0     | 
| Handler_write              | 60    | 
+----------------------------+-------+
15 rows in set (0.00 sec)

mysql> delete from test where (type=5) or (id=123456 and type=3);
Query OK, 0 rows affected (12.13 sec)

mysql> SHOW STATUS LIKE '%handler%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       | 
| Handler_delete             | 0       | 
| Handler_discover           | 0       | 
| Handler_prepare            | 0       | 
| Handler_read_first         | 1       | 
| Handler_read_key           | 2       | 
| Handler_read_next          | 0       | 
| Handler_read_prev          | 0       | 
| Handler_read_rnd           | 0       | 
| Handler_read_rnd_next      | 4500065 | 
| Handler_rollback           | 0       | 
| Handler_savepoint          | 0       | 
| Handler_savepoint_rollback | 0       | 
| Handler_update             | 0       | 
| Handler_write              | 75      | 
+----------------------------+---------+
15 rows in set (0.00 sec)

Now for DELETE which affects 500000 rows.

mysql> SHOW STATUS LIKE '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     | 
| Handler_delete             | 0     | 
| Handler_discover           | 0     | 
| Handler_prepare            | 0     | 
| Handler_read_first         | 0     | 
| Handler_read_key           | 0     | 
| Handler_read_next          | 0     | 
| Handler_read_prev          | 0     | 
| Handler_read_rnd           | 0     | 
| Handler_read_rnd_next      | 48    | 
| Handler_rollback           | 0     | 
| Handler_savepoint          | 0     | 
| Handler_savepoint_rollback | 0     | 
| Handler_update             | 0     | 
| Handler_write              | 60    | 
+----------------------------+-------+
15 rows in set (0.00 sec)

mysql> delete from test where (type=4) or (id=123456 and type=3);
Query OK, 500000 rows affected (1 min 6.46 sec)

mysql> SHOW STATUS LIKE '%handler%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       | 
| Handler_delete             | 0       | 
| Handler_discover           | 0       | 
| Handler_prepare            | 0       | 
| Handler_read_first         | 1       | 
| Handler_read_key           | 2       | 
| Handler_read_next          | 0       | 
| Handler_read_prev          | 0       | 
| Handler_read_rnd           | 0       | 
| Handler_read_rnd_next      | 4500065 | 
| Handler_rollback           | 0       | 
| Handler_savepoint          | 0       | 
| Handler_savepoint_rollback | 0       | 
| Handler_update             | 0       | 
| Handler_write              | 75      | 
+----------------------------+---------+
15 rows in set (0.00 sec)

Thank you for the report.

Verified as described.

Test case for the test suite:

CREATE TABLE test (id int not null  primary key, type1 int not null);
CREATE INDEX idx ON test (id, type1);

insert into test values(1,1), (2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

explain select * from test WHERE (type1=5) OR (type1=3 AND id=3);

SHOW STATUS LIKE '%handler%';

select * from test WHERE (type1=5) OR (type1=3 AND id=3);

SHOW STATUS LIKE '%handler%';

delete from test WHERE (type1=5) OR (type1=3 AND id=3);

SHOW STATUS LIKE '%handler%';

Omer,  

At the first glance these two bugs to not seem to be duplicates.

Is this bug really valid?   I thought it has to do a table or index scan to check condition "b=5"

I tried a bigger testcase on newer versions so it seems expected output to me, or do I miss something?
Probably reporter needs a better index on (type,id) instead of (id,type) ??

-- --------
drop table if exists t;
create table t (a int not null primary key, b int not null)engine=innodb;
create index ax on t(a, b);

set @a:=0;
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10);
replace into t select @a:=@a+1,floor(rand()*10) 
from t a,t b,t c,t d,t e,t f,t g,t h limit 500000;
select count(*) from t;
analyze table t;

explain select count(*) from t where b=5 or (b=3 and a=123456);
explain delete   from t where b=5 or (b=3 and a=123456);

flush status;
select count(*) from t where b=5 or (b=3 and a=123456);
show status like '%handler%';

set session optimizer_trace = "enabled=on";
flush status;
delete from t where b=5 or (b=3 and a=123456);
show status like '%handler%';
select * from information_schema.optimizer_trace\G
set session optimizer_trace = "enabled=on";

select version();
-- -----------

In Sveta's testcase the index shouldn't be:

CREATE INDEX idx ON test (id, type1);

  it should be:

CREATE INDEX idx ON test (type1, id);