CODEWITHSUNDEEP
mysqlperformance
GSto
GSto

Reputation: 42350

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

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?

Upvotes: 1

Views: 381

Answers (2)

Marc B
Marc B

Reputation: 360662

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838166

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

Upvotes: 2

Related Questions