• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Limit on number of values in mysql in clause

HI,
On this page :
http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in
its mentioned that :
The number of values in the IN list is only limited by the max_allowed_packet value.
what does this means ??
On my mysql i have the following value of this variable :
mysql> show variables like 'max_allowed_packet'
    -> ;
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.01 sec)

Open in new window

This is the default 4MB.
I dont understand the relation between the number of value and 4MB.

What limit does mysql produce on the number of values in the IN clause ?

Thanks
0
Rohit Bajaj
Asked:
Rohit Bajaj
  • 2
1 Solution
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
When you create an IN list you generally write strings that are buffered, so the limit is to have a buffer size not larger than max_allowed_packet.
Anyway there's not a sure limit number for arguments passed in the IN list: it depends on the size of the sent packet that is limited by max_allowed_packet.
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
0
 
Rohit BajajAuthor Commented:
So is it like If i am using a query : select * from note where id in (id1, id2,....)
And say each id is a string of 10 chars
so each id is approx 10 bytes and if the number of elements in the list is 1000. Then total buffer size will be = 10000 bytes = 10 KB ??
which is smaller than 4 MB so it is fine ?

Also the link mentions something about if you are using large columns...
So for queries like select * from note where a column type is TEXT this max_allowed_packet comes into play ???
If i have to move from TEXT to LONGTEXT do i need to change max_allowed_packet ??
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
@will be = 10000 bytes = 10 KB ??
which is smaller than 4 MB so it is fine ?

more or less yes.

To better understand how datas are stored  in blob or longtext field take a look here http://dev.mysql.com/doc/refman/5.7/en/blob.html and
also the Data Type Storage Requirements here http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now