MySQL中"in子句"中的项目数 [英] MySQL number of items within "in clause"
问题描述
我有三个表来定义用户:
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')
当前，我将子查询字符串存储在变量中，然后每次需要拉出用户列表时，将其动态插入到外部查询中.完成此操作后，我想到最好只存储实际的user_id s字符串".
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)";
最后是问题:
在MySQL IN条款中可以使用多少项?每次执行该外部查询时，必须存储实际的id而不是sub-sql语句，对吗?
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?
推荐答案
从一定数量开始，IN表更快.
Starting from a certain number, the IN tables are faster.
MySQL在其代码中包含一些内容，这使得在大量常量值上构建范围的速度比在嵌套循环中进行计算的速度慢.
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:
这篇关于MySQL中"in子句"中的项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
