CODEWITHSUNDEEP
mysql
eMRe
eMRe

Reputation: 3247

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

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

Upvotes: 24

Views: 30654

Answers (4)

Matt Gibson
Matt Gibson

Reputation: 14949

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.

Upvotes: 8

Rares
Rares

Reputation: 97

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..

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

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.

Upvotes: -1

Interrobang
Interrobang

Reputation: 17434

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.

Upvotes: 6

Related Questions