Fixed SQL value IN () compared to INNER JOIN

Question

In response to this SQL question, I came across the assertion that a statement with a fixed value IN() much slower than an INNER JOIN with the same content, to the extent that it is better to create a temporary table for the values ​​and join them. This is true (in general, with MySQL, any other SQL engine), and if so, why? Intuitively, IN should be faster - you compare a potential match with a fixed set of values ​​that are already in memory and in the required format, while with JOIN you will need to consult indexes, potentially load data from disk, and perform other operations that may be unnecessary with IN. Did I miss something important?

Note that unlike this question , and this is a lot of duplicates, I'm talking about IN() with a fixed set of values, not a subquery.

Answer 1

This refers to the length of the IN clause - and what is sometimes called BUG in MySQL.

MySQL seems to have a low threshold for IN clauses when it will be replaced with a TABLE / INDEX SCAN instead of collecting multiple partitions (one per IN element) and merging them.

With an INNER JOIN, you almost always have to use straight line by line in the JOIN collection, so it's sometimes faster

Refer to these MySQL manual pages.

• In subqueries
• Performance (Permanent List)

I could be wrong, since it seems that IN (constant value list) should always use a binary search on each element ...