问题描述
我需要有关 MySQL 中子选择性能的建议.由于我无法更改的原因，我无法使用 JOIN 来创建查询过滤器，我只能在 WHERE 中添加另一个 AND 子句.
I need advice regarding subselect performance in MySQL. For a reason that I can't change, I am not able to use JOIN to create quesry filter, I can only add another AND clause in WHERE.
性能如何:
select tasks.*
from tasks
where
some criteria
and task.project_id not in (select id from project where project.is_template = 1);
相比:
select tasks.*
from tasks, project
where
some criteria
and task.project_id = project.id and project.is_template <> 1;
请注意，is_template = 1 的项目数量相对较少，而 is_template <> 1 的项目数量可能很多.
Note that there is relatively small number of projects whete is_template = 1, and there could be large number of projects where is_template <> 1.
如果除了过滤器我不能改变任何东西，还有其他方法可以在没有子选择的情况下达到相同的结果吗?
Is there other way to achieve the same result without subselects if I can't change anything but and filter?
推荐答案
我认为第二种更高效，因为它只需要一次选择，但可以肯定的是，您应该解释每个查询并检查结果.
I believe that the second is more efficient as it requires only one select, but to be sure, you should EXPLAIN each query and check the results.
EXPLAIN select tasks.*
from tasks
where
some criteria
and task.project_id not in (select id from project where project.is_template = 1);
EXPLAIN select tasks.*
from tasks, project
where
some criteria
and task.project_id = project.id and project.is_template <> 1;
这篇关于MySQL子选择性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！