Mysql
用子查詢重寫查詢以提高性能
我的查詢看起來像這樣
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