Why using IN(...) when selecting on indexed fields, will kill the performance of SELECT query?

Question

Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

I found this here: https://wikis.oracle.com/pages/viewpage.action?pageId=27263381

Can you explain it? Why that will kill performance? And what should I use instead of IN. "OR" statement maybe?

Answer 1

To tell the truth, that statement contradicts to many hints that I have read in books and articles on MySQL.

Here is an example: http://www.mysqlperformanceblog.com/2010/01/09/getting-around-optimizer-limitations-with-an-in-list/

Moreover, expr IN(value, ...) itself has additional enhancements for dealing with large value lists, since it is supposed to be used as a useful alternative to certain range queries:

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants.

Still overusing INs may result in slow queries. Some cases are noted in the article.

Answer 2

Because MySQL can't optimize it.

Here is an example:

explain select * from keywordmaster where id in (1, 567899);

plan (sorry for external link. Doesn't show correctly here)

here is another query:

   explain 
   select * from table where id = 1
   union
   select * from keywordmaster where id = 567899

plan

As you can see in the second query we get ref as const and type is const instead of range. MySQL can't optimize range scans.