7

Possible Duplicate:
MySQL number of items within “in clause”
In mysql or postgres, is there a limit to the size of an IN (1,2,n) statement?

I build an "IN" list dynamically and I was wondering if there is a limit to the size of the IN clause in mysql.

SELECT foo FROM bar WHERE bar_key IN ('prod1', 'prod2', 'prod50', ...'last prod')

I imagine there is a limit to the total length of a SQL statement but I can't find it in the manual.

I do know that an alternative is to build a temp table and join with it, though I'm not sure where the performance tradeoff exists if I have to execute many INSERT statements. Of course if I could populate the temp table with a single INSERT that would be fast, but that's not possible in my case.

So my first question is what are the mysql limits and I'd appreciate if you can point me to relevant pages in the manual.

Community
  • 1
  • 1
David
  • 1,863
  • 2
  • 14
  • 12

1 Answers1

14

From the manual:

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

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • 2
    I find it interesting that both of the linked duplicate question's answers state that there is no limit when the manual clearly says that there is a limit. – xbrady Jan 10 '12 at 19:02
  • 1
    @xbrady: Agreed, I posted my answer to the other questions as well. – D'Arcy Rittich Jan 10 '12 at 19:02