Is there a performance difference in MySQL between IN and several OR clauses?

Question

Is there any performance difference between the two following queries:

SELECT foo,bar FROM table WHERE id IN (1,2,3);

and

SELECT foo,bar FROM table WHERE id = 1 OR id = 2 OR id = 3;

I'm sure the difference in negligible in a small example like this, but what about when the number of valid IDs is much larger?

Answer 1

The optimizer is more likely to be able to optimize WHERE id IN (1,2,3) than id = 1 OR id = 2 OR id = 3. I also think that using IN is more readable here.

Use WHERE id IN (1,2,3).

Answer 2

This was an assignment in a comp.sci database class I took, back in the dark ages. Turns out there's not too much of a performance difference between the two within measurement limits, but the ... IN (...) version does result in a much smaller query due to less characters required to construct it.