我应该尝试减少子查询中的选定行吗？ [英] Should I try to reduce selected rows in the subqueries?

问题描述

我有这个查询：

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = 29
AND question.amount IS NULL
AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
AND answer.id not in (
  select post_id
  from votes
  group by post_id
  having sum(value) < 0)

当前，该子查询实际上选择了890行。通过将这个（外部查询中的时间框架约束）也添加到子查询中，我可以将其减少到仅43行：

Currently that subquery selects 890 rows in reality. I can reduce it to just 43 rows by adding this (the time frame constraint which is in the outer query) also to the subquery:

. . . where  date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))

我可以进一步减少它（仅9行），方法是在该子查询的 WHERE 子句中添加以下行：

And I can reduce it more (to just 9 rows) by adding this line on the WHERE clause of that subquery:

. . . and table_code = 15

好，一些程序员认为：

在内部查询中添加where子句将减少行，但只会删除无关紧要的行。

Adding a where clause to the inner query would reduce the rows but would only remove rows that don't matter.

但是我本人相信为该内部查询添加 WHERE 子句会使它更快。好吧，我很困惑，应该在那些内部查询的 WHERE 子句中添加这些条件，还是没关系？

But I myself believe adding a WHERE clause for that inner query makes it more faster. Well I'm confuse, should I add those conditions on the WHERE clause of that inner query or it doesn't matter?

推荐答案

这个呢

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = 29
AND question.amount IS NULL
AND answer.date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
AND 0 <= (
  select sum(value)
  from votes
  where post_id = answer.id
)

？

有当然还有其他方法。

但是-您的子查询正在读取整个票表，而您只需要相关的表给作者答案。因此，您可以使用带有答案的联接来限制子查询的结果集：

However - your subquery is reading the entire votes table, while you only need those related to the authors answers. So you could limit the result set of your subquery using a join with the answers:

AND answer.id not in (
    select v.post_id
    from votes v
    join qanda a on a.id = v.post_id
    where a.author_id = 29
      and a.date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
      and a.related IS NOT NULL
    group by v.post_id
    having sum(v.value) < 0
)

这篇关于我应该尝试减少子查询中的选定行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！