CODEWITHSUNDEEP
mysqlsql
Julian
Julian

Reputation: 9130

What is the maximum number of items passed to IN() in MySQL

I am given a bunch of IDs (from an external source) that I need to cross-reference with the ones on our database and filter out those that fall between a certain date and are also "enabled" and some other parameters. I can easily do this by doing:

SELECT * FROM `table` WHERE `id` IN (csv_list_of_external_ids) 
    AND (my other cross-reference parameters);

And by doing this, of all those incoming IDs I will get the ones that I want. But obviously this is not a very efficient method when the external ids are in the thousands. And I'm not sure if MySQL will even support such a huge query.

Given that nothing can be cached (since both the user data and the external ids change pretty much on every query), and that these queries happen at least every 10 seconds. What other SQL alternatives are there?

Upvotes: 3

Views: 311

Answers (2)

Bohemian
Bohemian

Reputation: 425013

If you express it as a subquery:

SELECT * FROM table
WHERE id IN (SELECT ID FROM SOME_OTHER_TABLE)
AND ...

there is no limit.

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79586

I believe the only limit is the length of the actual query, which is controlled by the "max_allowed_packet" parameter in your my.cnf file.

Upvotes: 3

Related Questions