IN vs OR in the SQL WHERE clause
database, sql
Solution
I assume you want to know the performance difference between the following:
WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'
According to the manual for MySQL if the values are constant `IN` sorts the list and then uses a binary search. I would imagine that `OR` evaluates them one by one in no particular order. So `IN` is faster in some circumstances.
The best way to know is to profile both on your database with your specific data to see which is faster.
I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:
SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)
SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)
So in this case the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.
Problem
When dealing with big databases, which performs better: `IN` or `OR` in the SQL `WHERE` clause? Is there any difference about the way they are executed?