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 10:49	Modified:	26 Feb 12:38
Reporter:	Tilo Prütz
Status:	Verified
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.70, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Linux (Gentoo)
Assigned to:	Bugs System	Target Version:
Triage:	Triaged: D3 (Medium)

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.