6

In answer to this SQL question, I've encountered a statement that fixed-value IN() operator is much slower than INNER JOIN with the same content, to the point that it is better to create temporary table for the values and JOIN them. Is it true (in general, with MySQL, any other SQL engine) and if yes - why? Intuitively, IN should be faster - you're comparing the potential match with a fixed set of values, which are already in memory and in needed format, while with JOIN you'd have to consult the indexes, potentially load data from disk, and perform other operations that may be not needed with IN. Am I missing something important?

Note that unlike this question and it's many duplicates, I'm talking about IN() having fixed set of values, not subquery.

Community
  • 1
  • 1
StasM
  • 10,593
  • 6
  • 56
  • 103
  • This is definitely something that varies among DB platforms. In my experience with SQL Server and PostgreSQL, `IN` clauses on constants against indexes are very fast. – Pointy Jan 22 '11 at 23:09
  • The question you linked to is SQL Server. Not sure if you're already aware but MySQL can be quite catastrophically bad with [in with a sub query too](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190) – Martin Smith Jan 22 '11 at 23:15
  • @Martin original question (first link) was about MySQL. I know about MySQL troubles with subqueries, but I assumed it is fine with constant lists, that's why the claim in @DVK's answer surprised me. – StasM Jan 22 '11 at 23:18
  • Thought that might be the case but the link might be of interest to some. – Martin Smith Jan 22 '11 at 23:21

1 Answers1

7

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

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

With an INNER JOIN, it is almost always forced to use a direct row-by-row in JOIN collection, which is why it is sometimes faster

Refer to these MySQL manual pages

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

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262