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

Answer 1

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.

Answer 2

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.