How large can the array passed to SQL where column_value IN (Array) be?

Question

I am writing some code that will lookup for unique id's in a table with over 60,000 rows for the ones that are in an array using

mysql_query("SELECT * FROM users WHERE  unique_indexed_user_id IN('".join("', '", $array)."')") ;

the amount of this array is not limited to the end user so they might end up selecting a large array. thus I have to limit the array

   if( count($array_limit)>$array_limit ) 
  array_splice($array, $array_limit);

but I have no idea how to figure out the limit, this code is being used in a social network for people to invite their friends to something. so the bigger it is the better. however I don't know how big of an array mysql can handle?

what should the value of $array_limit be?

Answer 1

How many do you need, really?

I have used something like 10000 items in a query without problem (while moving data from one database to another, not in a live site), so you can certainly make a horribly big query.

There is however no point in getting that many items when you want to display them to a user, because noone scrolls through a list that large to the end, and having that much data would most likely slow down the user interface considerably.

You should use some kind of paging or append on request, so that you only get something like a hundred items each time.

Answer 2

The max length of a query passed to MySQL is the length of your max_packet_size variable. http://lists.mysql.com/mysql/187971

You also might want to take other things into consideration, such as the length of time it will take to parse and execute a query with that many IDs. I've taken this approach myself, and it started to seriously slow down after a few thousand IDs. You may want to try a JOIN, if you can.