Description:
MySQL failed to use index lookup for a query where there is a subquery and multi-element-tuple-list IN conditions. MySQL uses a full table scan for this query, causing significant performance problem.
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6), (2,7));
EXPLAIN
-> Filter: ((book.deleted = 1) and ((book.author,book.publisher) in ((1,6),(2,7)))) (cost=0.35 rows=1) (actual time=0.00441..0.00441 rows=0 loops=1)
-> Table scan on book (cost=0.35 rows=1) (actual time=0.00396..0.00396 rows=0 loops=1)
However, a query with a single element in that tuple list is understood correctly.
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6));
EXPLAIN
-> Filter: ((book.deleted = 1) and (book.publisher = 6)) (cost=0.35 rows=1) (actual time=0.0044..0.0044 rows=0 loops=1)
-> Index lookup on book using author (author=1) (cost=0.35 rows=1) (actual time=0.00357..0.00357 rows=0 loops=1)
This kind of query is generated by Hibernate, which I assume is unaware that MySQL is interpreting the query inconsistently.
How to repeat:
CREATE TABLE book (
author INT NOT NULL,
publisher INT NOT NULL,
deleted BOOLEAN NOT NULL,
INDEX (author),
INDEX (publisher)
);
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6), (2,7));
Description: MySQL failed to use index lookup for a query where there is a subquery and multi-element-tuple-list IN conditions. MySQL uses a full table scan for this query, causing significant performance problem. EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6), (2,7)); EXPLAIN -> Filter: ((book.deleted = 1) and ((book.author,book.publisher) in ((1,6),(2,7)))) (cost=0.35 rows=1) (actual time=0.00441..0.00441 rows=0 loops=1) -> Table scan on book (cost=0.35 rows=1) (actual time=0.00396..0.00396 rows=0 loops=1) However, a query with a single element in that tuple list is understood correctly. EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6)); EXPLAIN -> Filter: ((book.deleted = 1) and (book.publisher = 6)) (cost=0.35 rows=1) (actual time=0.0044..0.0044 rows=0 loops=1) -> Index lookup on book using author (author=1) (cost=0.35 rows=1) (actual time=0.00357..0.00357 rows=0 loops=1) This kind of query is generated by Hibernate, which I assume is unaware that MySQL is interpreting the query inconsistently. How to repeat: CREATE TABLE book ( author INT NOT NULL, publisher INT NOT NULL, deleted BOOLEAN NOT NULL, INDEX (author), INDEX (publisher) ); EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM book WHERE deleted = 1) d WHERE (d.author, d.publisher) in ((1, 6), (2,7));