用子查詢重寫查詢以提高性能

我的查詢看起來像這樣

SELECT topics.name, topics.topic_id, topics.creator, 
( SELECT COUNT(*) FROM answers 
WHERE answers.topic_id = topics.topic_id) 
AS replies FROM topics 
ORDER BY topics.bumped DESC, topics.date DESC LIMIT 500

但是它需要 0.6 秒，我猜這是因為計算答案的子查詢。還有其他方法可以以更好的性能實現此效果嗎？

topics: INDEX(bumped, date) -- in that order
answers: INDEX(topic_id)

SHOW CREATE TABLE如果您需要進一步討論，請提供。

500 是很多結果——不僅僅是一個自尊的 UI 應該返回。你會怎麼處理他們？

還要考慮這種方法：

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
   FROM  
   (
       SELECT  topic_id, count(1) AS ct
           FROM  answers 
           GROUP BY topic_id
   ) AS replies
   JOIN  topics  ON replies.topic_id = topics.topic_id
   ORDER BY  topics.bumped DESC, topics.date DESC
   LIMIT  500

由於只進入一次，它可能會執行得更快。answers

但是，如果在某些情況下可能有零回复，您需要一個LEFT JOIN：

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
   FROM  topics
   LEFT JOIN (
       SELECT  topic_id, count(1) AS ct
           FROM  answers 
           GROUP BY topic_id
             ) AS replies  ON replies.topic_id = topics.topic_id
   ORDER BY  topics.bumped DESC, topics.date DESC
   LIMIT  500

這確實需要一個相對較新的 MySQL 版本，以便可以自動為派生表提供索引。

哪種方法最快？我說不出來；全部嘗試。

通過 count (1) 代替 count(*)

SELECT  topics.name, topics.topic_id, topics.creator, 
   (
       SELECT  count(1)
           FROM  answers
           WHERE  answers.topic_id = topics.topic_id
   ) AS replies
   FROM  topics
   ORDER BY  topics.bumped DESC, topics.date DESC
   LIMIT  500 

引用自：https://dba.stackexchange.com/questions/171637