Solved

Limit on number of values in mysql in clause

Posted on 2016-09-30
3
60 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

790 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