Solved

Limit on number of values in mysql in clause

Posted on 2016-09-30
3
73 Views
Last Modified: 2016-10-06
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
Comment
Question by:Rohit Bajaj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
ID: 41823034
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
 

Author Comment

by:Rohit Bajaj
ID: 41823040
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41823059
@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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question