CODEWITHSUNDEEP
mysql
S. Imp
S. Imp

Reputation: 2895

What defines upper limit to the number of items in a MySQL WHERE clause?

I'm looking at some code written by someone else that takes an array and formulates a SELECT query for MySQL that typically has a lot of terms in a WHERE IN clause. The language it's written in happens to be PHP but I think that might be irrelevant to my question.

Assuming some array of $record_ids with easily hundreds and potentially thousands of integers in it, the array is created sort of thusly:

$sql = "SELECT * FROM my_table WHERE id IN (" . implode(",", $record_ids) . ")";

This will result in a query that might look like this

SELECT * FROM my_table WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,7,18,19, 20, 21, 22);

Except it might have literally thousand of IDs in the IN clause. My question is does MySQL enforce some upper limit to the number of terms you can put in a WHERE IN clause? If not, surely there must be some constraint or limit on how large this query can go -- what is that limit?

Upvotes: 0

Views: 456

Answers (2)

LSerni
LSerni

Reputation: 57418

That would be the size of the query packet, usually in the order of the megabytes.

With thousands of values, perhaps a temporary table to be joined could be advantageous. I've never tested this, though.

Update: the answer has been already given, and also the performance question has been answered. Placing the items in a temporary tables is better as soon as the number of the items grows above a couple dozens.

Also, this second solution might be more easily ported to other RDBMS (or MySQL configurations!) with more stringent limits. Imagine needing a 8M packet size and the hosting will only supply, say, 4.

Upvotes: 1

Parnik Kumar Gaurav
Parnik Kumar Gaurav

Reputation: 99

In SQL queries when we are writing the query like select * from table where id in (DATA); This point of time this is not just the custom user defined DATA but it can be a separate subquery from a database as well and a query can give you any number of results. As we do not have any upper limit in the select statement and it can fetch the entire number of records available in the database. So the final conclusion is there is no such upper limit thing is applied to your query as DATA can be a select query. I hope this explanation will be helpful to you.

Upvotes: 0

Related Questions