作者danielguo (Daniel Guo)
看板Database
标题Re: [MySQL]求留言板查询语法优化
时间Tue May 31 14:54:37 2011
照叙述看来, 这个查询应该很常用
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)
1F：推 mrbigmouth:大推！超详细超明白！ 大感谢m(_ _)m 05/31 20:16