Aug 26

MySQL - Using the “IN” clause

Posted in: Blog |
Tags: Database, MySQL, Performance, 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!

4 Comments to “MySQL - Using the “IN” clause”

• Enterprise headlines and summaries, 2009-08-26 « Next Gen Enterprise says:

  August 27, 2009 at 10:46 pm

  [...] MySQL – Using the “IN” clause 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: [...]
• Ashitkin Alexander says:

  August 28, 2009 at 10:22 am

  in clause in sql? AMAZING!
• Chris says:

  August 29, 2009 at 3:17 am

  I’ve had the opposite results for queries with 20 - 100 or so PK IDs. Doing “id=1 or id=2 …” gave me a big boost in performance. [The tables in question are anywhere from 100K to 2M records].

  I guess it’s worth mentioning that benchmarking the two would be a good idea…

  Another thing to watch out for is network I/O for these queries, regardless of using “IN” or a string of “OR”s. A good way around that might be to create a tmp table, fill it and do an inner join… keeping all the data on the DB servers and not moving it to and from the web / app servers

  Very nice article, btw
• George Truong says:

  August 29, 2009 at 9:18 pm

  Chris, thanks for the comment. But my disclaimer is that this is for beginners.

  That’s really interesting. I’ve never personally experienced an instance where “id=1 or id=2…” is faster, but then again if a query is slower I usually look at other areas. That would be an interesting case to look at. I would definitely need to run some benchmarks.

  You are absolutely right about keeping all the data on the DB servers with a temporary table. If you could help it, it’s good to only send data you need to your web app.