123

Hey, I have to use IN condition in my MySQL statement with a large set of ids.

Example

SELECT * FROM users WHERE id IN (1,2,3,4...100000)

Is there a limit if items the IN statement can have?

xpepermint
  • 35,055
  • 30
  • 109
  • 163
  • 1
    Possible duplicate of [MySQL number of items within "in clause"](http://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause) – Metro Smurf Oct 20 '16 at 12:08

3 Answers3

143

No there isn't, check the manual about the IN function:

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

Progman
  • 16,827
  • 6
  • 33
  • 48
  • so if I have, for example, an update with `where id IN (14000 ids)`, it will work until update query is less than 64MB (which is the default value), right? – Oleg Abrazhaev Oct 21 '20 at 19:11
  • 1
    @OlegAbrazhaev The `IN` keyword does not have any build-in limits, however there might be other limits like `max_allowed_packet` which might indirect limit the number of values. If you set this value to 1024 (bytes) you can effectively use only a limited number of values. – Progman Oct 21 '20 at 19:31
  • @Progman do you have a source for the indirect limiting of the query values? – Vin Shahrdar Mar 22 '21 at 14:24
  • @VinShahrdar The quote in the answer is from the official MySQL manual. – Progman Mar 22 '21 at 17:41
  • 1
    I understand that, but the official manual doesn't have any details about what happens when you go over the max_allowed_packet limit. I was troubleshooting a similar issue, where a query uses an IN operator with a ton of values in it (more than 100,000) and I was noticing some of the expected items aren't coming back, which led me to this thread! – Vin Shahrdar Mar 22 '21 at 18:15
  • @VinShahrdar When you hit the limit you get an error message like `ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes`. So it looks like the query will fail completely in that case. As your queries succeeds (but return unexpected rows) the issue might not be the `max_allowed_packet` setting. – Progman Mar 22 '21 at 18:27
  • @Progman so we truncated that table that was causing the IN operator in the query to have more than 100,000 elements (which were mostly duplicates). After that, with only 500 records in the IN operator, the query returns the expected items. That's pretty crazy! – Vin Shahrdar Mar 22 '21 at 22:11
25

As far as I know in mysql, there is no limit for items in the IN statement.

In oracle altough, there is a limit of 1000 items in the IN statement.

But more the items in IN, your query performance will slow down unless that column is indexed.

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • 6
    depends, if the IN is search primary key, is fast cause is return from index – ajreal Nov 25 '10 at 10:01
  • For comparison, SQLServer (MSSQL) has a limitation of around 2100 parameters in the total query, including in parameters. Postgres has no limit. SQLite has no limit. – Kiruahxh Mar 07 '23 at 14:25
3

enter image description here

1073741824 This is the limit given in Mysql docs

praveen
  • 330
  • 2
  • 8
  • 2
    thank you for sharing this, I guess one major reason this is downvoted is because it does not explain that the quantity of elements within the IN condition of the querry is not directly limited , but indirectly by the overall querry size which this config parameter is for. Anyway I find it usefull to find the default setting here, thank you – Keywan Ghadami Jul 21 '21 at 06:49