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?

Similar to http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance — Mark Byers, Sep 08 '10 at 20:00

score 2 · Accepted Answer · answered Sep 08 '10 at 14:18

2

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).

answered Sep 08 '10 at 14:18

Mark Byers

811,555
193
1,581
1,452

score 0 · Answer 2 · answered Sep 09 '10 at 00:10

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.

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

2 Answers2