MySQL “IN” operator performance on (large?) number of values

by

Generally speaking, if the IN list gets too large (for some ill-defined value of ‘too large’ that is usually in the region of 100 or smaller), it becomes more efficient to use a join, creating a temporary table if need so be to hold the numbers.

If the numbers are a dense set (no gaps – which the sample data suggests), then you can do even better with WHERE id BETWEEN 300 AND 3000.

However, presumably there are gaps in the set, at which point it may be better to go with the list of valid values after all (unless the gaps are relatively few in number, in which case you could use:

WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836

Or whatever the gaps are.

More Related Contents:

  1. MYSQL OR vs IN performance
  2. SQL Performance UNION vs OR
  3. What is this operator in MySQL?
  4. What is better in MYSQL count(*) or count(1)?
  5. MySQL: Inner join vs Where [duplicate]
  6. Does Order of Fields of Multi-Column Index in MySQL Matter
  7. Recreating a query using INNER JOIN? [duplicate]
  8. Create a Cumulative Sum Column in MySQL
  9. Subqueries vs joins
  10. Equivalent of explode() to work with strings in MySQL
  11. MySQL “WITH” clause
  12. mysql select dynamic row values as column names, another column as value
  13. MySQL DELETE FROM with subquery as condition
  14. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  15. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  16. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  17. Simple Random Samples from a Sql database
  18. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  19. How do I use properly CASE..WHEN in MySQL
  20. How to get the latest record in each group using GROUP BY? [duplicate]
  21. When to use SELECT … FOR UPDATE?
  22. Setting column values as column names in the SQL query result
  23. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  24. SQL unique varchar case sensitivity question
  25. How do I count columns of a table
  26. Laravel join with 3 Tables
  27. How to regex in a MySQL query
  28. MySQL 5.6 – DENSE_RANK like functionality without Order By
  29. Syntax error on MERGE statement
  30. mySQL query to search all tables within a database for a string?

Leave a Comment