CODEWITHSUNDEEP
mysqlindexingsql-delete
Ярослав Гойса
Ярослав Гойса

Reputation: 183

Mysql does not use index

I have table that has composite PK.

CREATE TABLE `tag_value_copy` (
    `tag_id` INT(11) NOT NULL,
    `created_at` INT(11) NOT NULL,
    `value` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`tag_id`, `created_at`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

When I execute following query 

DELETE FROM tag_value_copy WHERE (tag_id, created_at) IN ((1,2), (2,3), ..., (5,6))

mysql does not use index and goes through all rows. But why?

enter image description here

EXPLAIN SELECT * FROM tag_value_copy WHERE (tag_id,created_at) in ((1,1518136666), (2,1518154836)) do NOT use an index as well.

enter image description here

UPD 1

show index from tag_value_copy

enter image description here

UPD 2

explain delete from tag_value_copy where (tag_id=1 and created_at=1518103037) or (tag_id=2 and created_at=1518103038)

enter image description here

Upvotes: 3

Views: 105

Answers (1)

Rick James
Rick James

Reputation: 142228

The Why -- MySQL's optimizer does nothing toward optimizing (a, b) IN ((1,2), ...).

The Workaround -- Create a table with the pairs to delete. Then JOIN using an AND between each of the 2 columns.

None of these help: OR, FORCE INDEX.

Why the heck do you have PRIMARY KEY (tag_id, created_at) ? Are you allowing the same tag to be entered multiple times?

Upvotes: 1

Related Questions