MySQL IN clause: max number of arguments

Question

Say you have the following query:

SELECT * FROM table1 WHERE table1.id IN (1, 2, 3, 4, 5, ..., 999999)

What is a reasonable maximum for the number of items in the IN clause? I'm using Sphinx to generate full-text search results and inserting the IDs into a MySQL query. Is this an acceptable way to do it?

Answer 1

You can also have the IN clause take the results of a query, such as:

SELECT * FROM table1 
WHERE table1.id IN 
    (
   SELECT id from table2
    )

That way, you don't need to generate a text string with all the possible values.

In mysql, you should be able to put as many values in the IN clause as you want, only constrained by the value of "max_allowed_packet".

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

Answer 2

MariaDB (10.3.22 in my case) has a limit of 999 parameters to IN() before it creates a materialized temporary table, resulting in possibly much longer execution times. Depending on your indices. I haven't found a way to control this behaviour. MySQL 5.6.27 does not have this limit, at least not at ~1000 parameters. MySQL 5.7 might very well have the same "feature".

I ended up doing a series of where id = a or id = b ... but it also works fine by using series of where id in(a, b) or id in(c, d) ....

Answer 3

You have to add laravel row query and then add NOT IN condition into this:

$object->whereRaw('where id NOT IN (' . $array_list . ') ');

This works for my code, I have 1087 records.

Answer 4

From my experience the maximum values is 1000 values in clause IN ('1',....,'1000'), I have 1300 value in my excel sheet,I put them all into IN ,MySQL return only 1000 .