Optimizing IN() queries against a compound index
Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key:
explain select * from tbl1
where (col1, col2) in (
(732727758,102),(732728118,102),(732728298,102),(732728478,102),
(732735678,102),(962074728,102),(964153098,102),(2027956818,102),
(2034233178,102),(2034233538,102))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1379
Extra: Using where
Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table:
explain select * from tbl1
where (col1=732727758 and col2=102)
OR (col1=732728118 and col2=102)
OR (col1=732728298 and col2=102)
OR (col1=732728478 and col2=102)
OR (col1=732735678 and col2=102)
OR (col1=962074728 and col2=102)
OR (col1=964153098 and col2=102)
OR (col1=2027956818 and col2=102)
OR (col1=2034233178 and col2=102)
OR (col1=2034233538 and col2=102)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: NULL
rows: 10
Extra: Using where
I filed a bug on that back in the day (2005? 2006?), it was closed as ‘duplicate’.
domas
22 Aug 12 at 12:22 pm
Yes, this problem is an old favorite. I haven’t tested this on MariaDB. Has MariaDB fixed this yet?
Xaprb
22 Aug 12 at 1:14 pm
Baron, Domas
How about joint sponsorship of this optimization by Percona/Facebook? I believe 10K (40 hrs) would be enough.
Regards,
Igor.
Igor Babaev
22 Aug 12 at 7:33 pm
Haven’t had a chance to install the new version yet … does the same hold true if the query is written as:
select * from tbl1 where col1 in (…) and col2 = 102
I know this is a special case, but I’m just curious.
Fenway
22 Aug 12 at 7:34 pm
http://bugs.mysql.com/bug.php?id=31188
(Optimizer does not do index range scans using predicates in IN lists)
sbester
23 Aug 12 at 11:31 am