MySQL

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 Web Developer at Shopit

Shopit is a simple to use commerce platform that enables efficient sharing and publishing of products and information anywhere online, including popular destination properties like MySpace, FaceBook, Bebo, Friendster, Blogger, LiveJournal, Xanga and many more. Visit Shopit.

as Web Developer at Diamond Creative

Find, Learn, Save. Bills.com fundamental goal is to help consumers learn about the confusing world of personal finance, and give them the resources to help them save money. Visit Bills.com.