Mysql does not use index

Question

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?

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

UPD 1

show index from tag_value_copy

UPD 2

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

Possible duplicate of [MySQL not using indexes with WHERE IN clause?](https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause) — Jorge Campos, Feb 22 '18 at 22:52
I am not sure, I even can`t use 'force index' on delete query. If I change delete from ... to select * from .... Be sure, it uses an index. — Ярослав Гойса, Feb 23 '18 at 00:01
I cannot reproduce your claims on a table created with your DDL. Both `select` and `delete` use the same PK index. There must be something you're not telling us. What is the cardinality of `tag_id`? How many `...`s are in your `in` list? — mustaccio, Feb 23 '18 at 02:49
It looks like the cardinality of tag_id is super low, but the query suggest that it should be able to use created_at. Maybe MySQL simply has not optimized this usage of `IN` ? — Evert, Feb 23 '18 at 09:13

score 1 · Answer 1 · answered Mar 01 '18 at 17:14

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?

Mysql does not use index

1 Answers1