mysql - 需要一些帮助来优化此 mySQL 查询

Question

我运行此查询是为了从 phpbb3 获取 5 个最近的帖子/主题。该论坛拥有超过180,000条记录。当前的查询平均需要 20 秒才能完成。我有什么想法可以优化它以使其更快吗？

SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_users u
WHERE t.topic_id = p.topic_id AND
f.forum_id = t.forum_id AND
t.forum_id != 4 AND
t.topic_status <> 2 AND
p.post_id = t.topic_last_post_id AND
p.poster_id = u.user_id
ORDER BY p.post_id DESC LIMIT 5;

Answer 1

首先，我认为 t.topic_id = p.topic_id 子句是多余的，因为它已经由 p.post_id = t.topic_last_post_id 隐含了。所以尝试这个简化版本:

SELECT
    t.topic_id,
    t.topic_title,
    t.topic_last_post_id,
    t.forum_id,
    p.post_id,
    p.poster_id,
    p.post_time,
    u.user_id,
    u.username
FROM phpbb_forums f
JOIN phpbb_topics t ON f.forum_id = t.forum_id
JOIN phpbb_posts p ON t.topic_id = p.topic_id
JOIN phpbb_users u ON p.poster_id = u.user_id
WHERE
    t.forum_id != 4 AND
    t.topic_status != 2
ORDER BY p.post_id DESC LIMIT 5;

其次，(这可能是速度缓慢的原因)确保您在以下列上有索引:f.forum_id、t.forum_id、 t.topic_id、p.topic_id、p.poster_id、u.user_id 和 t.topic_status .

(<> 和 != 等效)