mysql - 如何减少子查询中的JOIN？

Question

这是我当前的查询:

SELECT q.type,
      q.author_id author,
      q.deleted,
      q.deleter_id,
      COUNT(DISTINCT a.id) answers_num,
      COUNT(DISTINCT v.id) votes_num,
FROM qanda q
LEFT  JOIN qanda a ON q.id = a.related
LEFT  JOIN votes v on q.id = v.post_id
WHERE q.id = ?

效果很好。只是我需要在 SELECT 语句中添加一件事(它通过 EXISTS 返回一个 bool 值)。这:

EXISTS (SELECT a.id, sum(vot.value) total_votes
        FROM qanda q
        LEFT JOIN qanda a on q.id = a.related
        LEFT JOIN votes vot ON a.id = vot.post_id
        WHERE q.type = 0         -- this is a question
          AND a.related = 1315   -- getting answers
        GROUP BY a.id
        HAVING total_votes
        ORDER BY total_votes DESC
        LIMIT1
) as HasAUpvotedAnswer

好的，效果也很好。但我担心表演。正如您所看到的，上面两个查询的 JOIN 是相似的(最终将合并)。如何才能让它们更加优化呢？

换句话说，如何在最佳情况下组合上述两个查询？

Answer 1

查看您的代码可能是您可以使用条件求和的单个查询

SELECT q.type,
      q.author_id author,
      q.deleted,
      q.deleter_id,
      COUNT(DISTINCT a.id) answers_num,
      COUNT(DISTINCT v.id) votes_num,
      IF (SUM(
        CASE WHEN q.type  = 0 AND a.related = 1315
          THEN v.value  else  0 END) > 0,1,0) 
      HasAUpvotedAnswer
FROM qanda q
LEFT  JOIN qanda a ON q.id = a.related
LEFT  JOIN votes v on q.id = v.post_id
WHERE q.id = ?