IN vs OR in the SQL WHERE Clause

by

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.

More Related Contents:

  1. How does database indexing work? [closed]
  2. Insert into … values ( SELECT … FROM … )
  3. Refactor a PL/pgSQL function to return the output of various SELECT queries
  4. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  5. Optimise PostgreSQL for fast testing
  6. Querying data by joining two tables in two database on different servers
  7. Are soft deletes a good idea? [duplicate]
  8. How to delete duplicate rows without unique identifier
  9. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  10. Storing money in a decimal column – what precision and scale? [closed]
  11. What is the difference between using a cross join and putting a comma between the two tables?
  12. When or why would you use a right outer join instead of left?
  13. PostgreSQL parameterized Order By / Limit in table function
  14. Pivot Table and Concatenate Columns
  15. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  16. How do I interpret precision and scale of a number in a database?
  17. Procedure or function !!! has too many arguments specified
  18. The backend version is not supported to design database diagrams or tables
  19. SQL query that gives distinct results that match multiple columns
  20. Android multiple databases open
  21. What is it exactly a BLOB in a DBMS context
  22. Composite Primary Keys : Good or Bad?
  23. Is there a version control system for database structure changes?
  24. Using a database table as a queue
  25. What is the best way to implement soft deletion?
  26. Query a Table’s Foreign Key relationships
  27. Historical / auditable database
  28. Postgres: convert single row to multiple rows (unpivot)
  29. How to store a list in a db column
  30. Copy table structure to new table in sqlite3

Leave a Comment