Why does using IN (...) when selecting by indexed fields lead to a decrease in the performance of the SELECT query?

Question

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

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

Can you explain this? Why does this kill productivity? And what should I use instead of IN. "Or maybe?

Answer 1

Honestly, this statement contradicts many of the hints that I 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/

In addition, expr IN (value, ...) itself has additional improvements for working with large lists of values, since it should be used as a useful alternative to some range queries:

If all values ​​are constants, they are evaluated according to the type of expression and sorted. Then, the object is searched using binary search. This means that IN is very fast if the list of IN values ​​consists entirely of constants.

Still, using INs can lead to slow queries. Some cases are noted in the article .

Answer 2

Because MySQL cannot optimize it.

Here is an example:

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

plan (sorry for the external link. Not displayed here)

here is another request:

  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 - const instead of a range. MySQL cannot optimize range scans.

Answer 3

Prior to MySQL 5.0, it seems that mySQL will use only one index per table. So, if you have SELECT * FROM tbl WHERE (a = 6 OR b = 33) , it can sensitively use either index or index b, but not both. Please note that it contains fields, the plural. I suspect that advice has come from that time, and the workaround was to combine OR results, for example:

 SELECT * FROM tbl WHERE (a = 6) UNION SELECT * FROM tbl WHERE (b = 33) 

Answer 4

I believe that IN is treated in the same way as the OR group, so using ORs will not help.

An alternative is to create a temporary table to store the values ​​of your IN clause, and then join this temporary table in your SELECT.

For instance:

 CREATE TEMPORARY TABLE temp_table (v VARCHAR) INSERT INTO temp_table VALUES ('foo') INSERT INTO temp_table VALUES ('bar') SELECT * FROM temp_table tmp, orig_table orig WHERE temp_table.v = orig.value DROP TEMPORARY TABLE temp_table