How many values in an "in" clause is too many in a SQL query?

Question

I have a SQL query that uses the values of an array in its WHERE clause:

 $ids = array 
         ( 
           [0] => 1 
           [1] => 2 
           [2] => 5 
         ) 

 $ids = join(',',$ids);   
 $sql = "SELECT * FROM cats WHERE id IN ($ids)";

My question is how many ids are too many?
Will it effect the speed?

Thanks all

It all depends on your table structure and indexes. And obviously as Sergei stated the more the slower. — aknosis, Dec 27 '11 at 23:33
possible duplicate of [MySQL number of items within "in clause"](http://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause) — Lieven Keersmaekers, Dec 27 '11 at 23:36

score 7 · Answer 1 · answered Feb 17 '12 at 13:33

7

Oracle has a limit of 1000, which I've hit before. MySQL doesn't seem to mind. The best solution is not to use an IN clause for that large a dataset though. Where do the ids come from? If from the same DB, then see if you can use a subquery instead that searches based on one parameter e.g. userid to find the linked ids. This will be far more efficient as MySQL can do a join internally using indexes.

answered Feb 17 '12 at 13:33

Matt Gibson

14,616
7
47
79

1

What if the ids do not come from the same DB? Example: a set of ids is received from a different database, possibly from a NoSQL database or other API? – Aposhian Jan 19 '21 at 22:28

score 6 · Answer 2 · answered Dec 27 '11 at 23:33

6

The more data you select, the longer this takes, but your primary concern should not be the number of ids you SELECT. Instead, you should ensure that your id has an INDEX on it or that it is the PRIMARY KEY. This will make lookups fast no matter how many ids you're grabbing.

answered Dec 27 '11 at 23:33

Interrobang

16,984
3
55
63

1

Generally yes. But if you combine `IN` with `ORDER` and `LIMIT`, you get problems on large datasets. – Sergio Tulentsev Dec 27 '11 at 23:35

Sergio Tulentsev · Answer 3 · 2011-12-27T23:37:38.893

0

The more values you put into IN, the slower it will run, obviously.

"how many is too many" is a function of many factors. For example, your dataset size. Or how the rest of your query looks like.

edited Dec 27 '11 at 23:37

answered Dec 27 '11 at 23:32

Sergio Tulentsev

226,338
43
373
367

score 0 · Answer 4 · answered Feb 17 '12 at 13:24

think of..if you have to write 5 pages or 10 what takes longer...it's normal if you have a huge amount of ids to take the query longer.You should look and assure that your data is not repeating because that will make the query take longer for unnecessary data..

How many values in an "in" clause is too many in a SQL query?

4 Answers4

Linked