1

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?

Steve B.
  • 55,454
  • 12
  • 93
  • 132

2 Answers2

4

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.

chaos
  • 122,029
  • 33
  • 303
  • 309
  • 2
    Thank you for posting from the doc - I don't know why people don't do this immediately for questions like this. – Sampson Jul 02 '09 at 15:29
2

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.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • How exactly would that be done? "DELETE FROM FOO WHERE ID in bar.id;"? – Thomas Jul 02 '09 at 15:30
  • 1
    DELETE FROM FOO WHERE ID IN (SELECT ID FROM BAR) – Otávio Décio Jul 02 '09 at 15:32
  • Your solution is useful in that there are other ways to accomplish this; however I'm actually looking for information on big, honking "IN" clauses, and whether or not they'll make the db explode.Additionally, given that you'll have to do an insert and then a join, how will that impact the performance? If I were checking against a much larger number of id's this might be a good solution, though. – Steve B. Jul 02 '09 at 18:58
  • @Steve - I understand. It just gives me the creeps. – Otávio Décio Jul 02 '09 at 19:35