Scalability

I was helping someone debug a slow query today when I noticed a common mistake that a lot of developers do. He was trying to get a list of users given a list of user id numbers.

The query looks something like this:

SELECT * FROM users
WHERE
  id = 1 OR
  id = 2 OR
  id = 3 OR
  id = 4 OR
  id = 5

For small data sets, SQL queries like the one above are fine. There isn’t much data for MySQL to search through and retrieve. You wouldn’t notice much of a performance hit on your servers at all.

But what if you had over a million users and you need to retrieve records from a list of 50,000 user id numbers? A better way to rewrite the query is to use the IN clause.

SELECT * FROM users
WHERE
  id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,5000001)

You can also pass in strings and other data types:

SELECT * FROM users
WHERE
  first_name IN ('John', 'Jane', 'James', 'Jack')

You can also do NOT IN:

SELECT * FROM users
WHERE
  first_name NOT IN ('Alex', 'Abby', 'Andy', 'Audrey')

Hope this helps improve your data retrieval times. If anything, it definitely makes for easier to read queries.

Happy querying!

as Senior Software Engineering Contractor at Extract Value

IASG.com is the largest, most comprehensive database of Managed Futures and an advanced yet easy-to-use platform for building portfolios of Commodity Trading Advisors. Visit live site or beta site.