MySQL number of items within "in clause"

Question

I have three tables to define users:

USER: user_id (int), username (varchar) USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar) USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar) 

I'd like to create a middle tier user that has certain access to other users within the application. To determine which users the logged in use can access, I am using a subquery like the following:

SELECT user_id FROM user WHERE user_id       IN (SELECT user_id           FROM user_metadata           WHERE user_metadata_field_id = 1 AND field_value = 'foo') 

Currently I am storing the subquery string in a variable and then dynamically inserting it into the outer query each time I need to pull a list of users. After doing this I thought, "it has got to be better to just store a string of the actual user_ids".

So instead of storing this in a variable...

$subSql = "SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = 'foo'"; 

... I actually perform the query and store the result like this...

$subSql = "12, 56, 89, 100, 1234, 890"; 

Then when I need to pull a lit of users that the logged in user has access to, I can do so with:

$sql = "SELECT user_id FROM user WHERE user_id IN ($subSql)"; 

And finally the questions:

How many items can you use in a MySQL IN CLAUSE? Storing the actual ids instead of the sub-sql statement has got to be faster for performing that outer query each time, right?

Answer 1

From the manual:

The number of values in the IN list is only limited by the max_allowed_packet value.

Answer 2

Starting from a certain number, the IN tables are faster.

MySQL has something inside its code that makes building a range over a large number of constant values slower than doing the same in a nested loop.

See this article in my blog for performance details:

• Passing parameters in MySQL: IN list vs. temporary table