Re: [MySQL]求留言板查詢語法優化

看板Database (資料庫)作者danielguo (Daniel Guo)時間14年前 (2011/05/31 14:54)推噓1(1推 0噓 0→)

留言1則, 1人參與討論串2/2 (看更多)

照敘述看來, 這個查詢應該很常用 board 資料表要有一個索引涵蓋 `rid`, `time` 這兩個欄位原先的作法等於: SELECT `board`.* FROM (SELECT DISTINCT `tID` FROM (SELECT IFNULL(`rid`,`id`) AS `tid`, `time` FROM `board` ORDER BY `time` DESC) AS `T1` LIMIT 3) AS `T2` LEFT JOIN `board` ON (`board`.`id` = `T2`.`tid`) OR (`board`.`rid` = `T2`.`tid`) ORDER BY `board`.`time` DESC 最簡單效能也最好的的改法, 就是把標題抓出來變成另一個表, 在標題表中記錄該標題的最後更新時間如果沒辦法改的話, 首先先分別抓出近期的標題第一篇和後續文章原先的作法最內層沒有限制查詢筆數, 傳回的筆數就是 board 中全部的筆數改寫1: SELECT IFNULL(`rid`,`id`) AS `tid`, MAX(`time`) AS `ttime` FROM `board` GROUP BY `tid` ORDER BY `ttime` DESC LIMIT 3 改寫2, 分別查詢標題第一篇和回應: (SELECT `rid` AS `id`, MAX(`time`) AS `time` FROM `board` WHERE `rid` IS NOT NULL GROUP BY `rid` ORDER BY `time` DESC LIMIT 3) UNION (SELECT `id`, `time` FROM `board` WHERE `rid` IS NULL ORDER BY `time` DESC LIMIT 3) 我不確定改寫2會不會比較快, 有時候 MySQL 對查詢的最佳化不太好. 改寫2外面要再包一層篩選, 但對效能沒有影響, 因為改寫2最多只會傳回6個資料列最後修改排序的條件, 讓同標題的在一起, 第一篇在最上面, 回覆依時間排序改寫1: SELECT `board`.* FROM (SELECT IFNULL(`rid`,`id`) AS `tid`, MAX(`time`) AS `ttime` FROM `board` GROUP BY `tid` ORDER BY `ttime` DESC LIMIT 3) AS `T1` LEFT JOIN `board` ON (`board`.`id` = T1.`tid`) OR (`board`.`rid` = T1.`tid`) ORDER BY T1.`ttime` DESC, `board`.`rid` ASC, `board`.`id` DESC 改寫2: SELECT `board`.* FROM (SELECT `id` AS `tid`, MAX(`time`) AS `ttime` FROM ((SELECT `rid` AS `id`, MAX(`time`) AS `time` FROM `board` WHERE `rid` IS NOT NULL GROUP BY `rid` ORDER BY `time` DESC LIMIT 3) UNION (SELECT `id`, `time` FROM `board` WHERE `rid` IS NULL ORDER BY `time` DESC LIMIT 3)) AS `T2` GROUP BY `id` ORDER BY `ttime` DESC LIMIT 3) AS `T1` LEFT JOIN `board` ON (`board`.`id` = T1.`tid`) OR (`board`.`rid` = T1.`tid`) ORDER BY T1.`ttime` DESC, `board`.`rid` ASC, `board`.`id` DESC -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 71.93.105.54 ※ 編輯: danielguo 來自: 71.93.105.54 (05/31 14:55)