Limits on number of values in "IN" queries.

Question

Given a query like the following:

DELETE FROM FOO WHERE ID in (1,2,3,4,....);

• Is there an upper limit to the number of values in the inclusion? (I've seen Oracle complain at 1000, but that was a long time ago. I have no idea if that was installation-dependant, oracle-version dependant, etc. etc., or how mysql limits this.

• What are the performance implications? Would it be faster to break up the set of values into smaller subsets and send multiple delete requests?

Answer 1

If you want to be sure that your statement will always work, put them in a temp table and do the IN statement against it.

Answer 2

See also http://stackoverflow.com/questions/1069415/t-sql-where-col-in (it's for t/sql, but the answers are general)

Answer 3

From the docs:

The number of values in the IN list is only limited by the max_allowed_packet value.

As to performance, I would break up the queries when they start approaching a thousand IDs or so. IN is pretty well-optimized, but it still doesn't seem like any good can come of throwing enormous lists at it.