mysql - 使用 ORDER BY 帮助优化 MySQL SELECT

Question

目前该表有以下索引:

1. forum_id_index
2. other_forum_id_index
3. forum_id_on_other_forum_id_index => [forum_id, other_forum_id]

查询:

SELECT `topics.*` 
FROM `topics` 
WHERE (table.forum_id = ? OR table.other_forum_id = ?) 
ORDER by sticky, replied_at DESC LIMIT 25

我已经尝试在以下内容上添加索引:

1. 粘
2. 回复于
3. [置顶，回复_at]
4. [forum_id, other_forum_id, sticky, replied_at]
5. [sticky, replied_at, forum_id, other_forum_id]

这是一个论坛，试图获得论坛中的前 25 个主题，但将 sticky 主题(sticky 是粘性/非粘性的二进制字段)放在顶部。

我已经阅读了几乎所有我能得到的关于优化 ORDER BY 的内容，但没有运气。这是在 MySQL 5.1，INNODB 上。任何帮助将不胜感激。

编辑

按照评论中的要求(抱歉，如果我做错了 - SU 上的新手)。目前EXPLAIN的结果:

id = 1

select_type = SIMPLE

table = topics

type = index_merge

possible_keys = index_topics_on_forum_id,index_topics_on_sticky_and_replied_at,index_topics_on_forum_id_and_replied_at,index_topics_on_video_forum_id,index_forum_id_on_video_forum_id,

keys = index_topics_on_forum_id,index_topics_on_video_forum_id

key_len = 5,5

ref = NULL

rows = 13584

Extra = Using union(index_topics_on_forum_id,index_topics_on_video_forum_id); Using where; Using filesort

SHOW INDEXES FROM topics 返回 https://gist.github.com/1079454 - 无法在此处很好地显示格式。

编辑 2

SELECT `topics`.*
FROM `topics`
WHERE topics.forum_id=4
ORDER BY sticky desc, replied_at DESC

运行速度极快(1.4 毫秒)。当我将 topics.forum_id 更改为 topics.video_forum_id 时查询也是如此 - 只是当我在查询中使用 or 将它们都放在一起时。

Answer 1

我觉得这个应该很快

索引:

ALTER TABLE `topics` 
    ADD INDEX `forum` (`forum_id` ASC, `sticky` ASC, `replied_at` DESC), 
    ADD INDEX `other_forum` (`other_forum_id` ASC, `sticky` ASC, `replied_at` DESC);

查询:

(
    SELECT `topics.*` 
    FROM `topics` USE INDEX (`forum`)
    WHERE `topics`.forum_id = ?
    ORDER by sticky, replied_at DESC 
    LIMIT 25
) UNION (
    SELECT `topics.*` 
    FROM `topics` USE INDEX (`other_forum`)
    WHERE `topics`.other_forum_id = ?
    ORDER by sticky, replied_at DESC 
    LIMIT 25
)
ORDER by sticky, replied_at DESC 
LIMIT 25