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

The max length of a query passed to MySQL is the length of your max_packet_size variable. https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

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.