mysql - 优化简单查询

Question

此查询将向您显示给定论坛中的所有未读主题。 EXPLAIN EXTENDED 的输出有点令人担忧。我想知道这里是否有人可以提供一些关于我如何优化的见解。

SELECT topic.*
FROM topic
INNER JOIN board ON topic.board_id = board.id OR topic.board_id = board.mirror_board_id
INNER JOIN category ON board.category_id = category.id
INNER JOIN group_assoc
 ON (
  group_assoc.board_id = board.id AND
  group_assoc.group_id IN (4,15,18,22) AND
  group_assoc.viewable = 1
 )
WHERE topic.last_post_time > 1288278402
AND category.forum_id = 2
AND board.id NOT IN(4,3)
AND NOT EXISTS (
    SELECT *
    FROM topic_read_assoc
    WHERE topic_id = topic.id
    AND member_id = 332
)
ORDER BY topic.last_post_time DESC

输出:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY category ref PRIMARY,forum_id_2 forum_id_2 4 const 5 100.00 Using temporary; Using filesort
1 PRIMARY board ref PRIMARY,mirror_board_id,category_id_2 category_id_2 4 source_forum.category.id 4 100.00 Using where
1 PRIMARY group_assoc ref board_id,board_id_2,board_id_3 board_id_3 4 source_forum.board.id 4 100.00 Using where; Using index
1 PRIMARY topic ALL board_id_2 NULL NULL NULL 2462 100.00 Range checked for each record (index map: 0x4)
2 DEPENDENT SUBQUERY topic_read_assoc ref topic_id topic_id 8 source_forum.topic.id,const 1 100.00 Using index

Answer 1

在主题 (last_post_time) 上创建索引。

您还可以从 EXISTS 子查询中删除 LIMIT 1，这是多余的。