mysql - 我应该尝试减少子查询中的选定行吗？

Question

我有这个查询:

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 行:

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

我可以通过在该子查询的 WHERE 子句上添加此行来减少更多(仅 9 行):

. . . and table_code = 15

好吧，有些程序员相信:

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

但我自己相信为该内部查询添加一个 WHERE 子句会使它更快。嗯，我很困惑，我应该在内部查询的 WHERE 子句中添加这些条件吗？还是没关系？

Answer 1

这个怎么样

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
)

？

当然还有其他方法。

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

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
)