0

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?

S. Imp
  • 2,833
  • 11
  • 24

2 Answers2

1

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.

Community
  • 1
  • 1
LSerni
  • 55,617
  • 10
  • 65
  • 107
  • This is a good answer, but Alex's linked answer is not relevant to MySQL and quite possibly baloney. I've run a test with a 500,000 ids in the clause and it ran just fine. I can't imagine Oracle would barf if MySQL does it just fine. I upped that id count to a million and got an error ("allowed memory exhausted") but it was a PHP memory error and due to a limitation in CodeIgniter rather than MySQL. – S. Imp Jan 07 '17 at 19:39
  • Thank you, I have edited my answer. Also I've thought a more likely scenario, as max packet size could be variable between installations. – LSerni Jan 07 '17 at 19:56
0

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.

Parnik Kumar Gaurav
  • 99
  • 1
  • 6