Recently, I was asked about an interesting case in ORDER BY … LIMIT optimization. Consider a table
create table tbl ( … KEY key1(col1, col2), PRIMARY KEY (pk) ) engine=InnoDB;
Consider queries like:
select * from tbl where col1=’foo’ and col2=123 order by pk limit 1; select * from tbl where col1=’bar’ and col2=123 order by pk limit 1;
These run nearly instantly. But, if one combines these two
queries with col1='foo' and col1='bar'
into one query with col1 IN ('foo','bar'):
select * from tbl where col1 IN (’foo’,'bar’) and col2=123 order by pk limit 1;
then the query is be orders of magnitude slower than both of the
queries with col1=const.
The first thing to note when doing investigation is to note that the table uses InnoDB engine, which has …
[Read more]