When is it considered too much for using strings in a MySQL IN clause?

Question

Please tell me from experience with using the "IN clause" in a MySQL query is considered to be too large of a list to push through to be considered good practice?

Example scenario is I have a string of user ID numbers that I will pull from a cache, it can range from anywhere from 1 number all the way up to being 5,000 numbers in this list and I will use a mysql query with this list like this example;

SELECT column FROM table 
WHERE userid 
IN ( 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 )

Can you give me your thoughts on this issue please?

Answer 1

I would not recommend using the in clause, the query will execute allot faster if you used a join instead.

What is your table structure, I'm guessing you have a table storing friend relations, can't you join on that instead?

SELECT * --you columns
FROM FriendLinks fl
Inner JOIN Users u on  fl.friendid = u.id
WHERE u.id = <your id>

or something like that

Answer 2

As long as your userid field is indexed, sounds like a good option.

Anyway, the real question here would be comparing the performance of IN against some other alternative. If that's the only way to get your data, there's not much you can do about it

Answer 3

It should be noted that some databases have limits on the number of entries within an IN clause - Oracle versions <= 10 are limited to 1000. I'm not sure about v11.

Answer 4

from a similar question, max length is configurable with a limit of 1GB in 5.0 according to mysql docs