Solved

Limit on number of values in mysql in clause

Posted on 2016-09-30
3
37 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
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
@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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now