sql - 优化 MySQL 查询以避免 "Using where; Using temporary; Using filesort"

Question

我使用 MySQL 为我的站点构建了一个自定义论坛。列表页面本质上是一个包含以下列的表格:主题、上次更新和# Replies。

数据库表有以下列:

id
name
body
date
topic_id
email

主题 的 topic_id 为“0”，回复 的 topic_id 是其父主题的。

SELECT SQL_CALC_FOUND_ROWS
    t.id, t.name, MAX(COALESCE(r.date, t.date)) AS date, COUNT(r.id) AS replies
FROM
    wp_pod_tbl_forum t
LEFT OUTER JOIN
    wp_pod_tbl_forum r ON (r.topic_id = t.id)
WHERE
    t.topic_id = 0
GROUP BY
    t.id
ORDER BY
    date DESC LIMIT 0,20;

此表中共有大约 2,100 个项目，查询通常需要高达 6 秒的时间。我在“topic_id”列中添加了一个 INDEX，但这并没有多大帮助。有什么方法可以在不进行重大重组的情况下加快此查询的速度吗？

编辑:还没有完全正常工作。我似乎无法让下面的示例正常工作。

Answer 1

SELECT  id, name, last_reply, replies
FROM    (
        SELECT  topic_id, MAX(date) AS last_reply, COUNT(*) AS replies
        FROM    wp_pod_tbl_forum
        GROUP BY
                topic_id
        ) r
JOIN    wp_pod_tbl_forum t
ON      t.topic_id = 0
        AND t.id = r.topic_id
UNION ALL
SELECT  id, name, date, 0
FROM    wp_pod_tbl_forum t
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    wp_pod_tbl_forum r
        WHERE   r.topic_id = t.id
        )
        AND t.topic_id = 0
ORDER BY
       date DESC
LIMIT 0, 20

如果你的表是MyISAM或者id不是PRIMARY KEY，你需要在(topic_id , id).

如果你的表是 InnoDB 并且 id 是一个 PRIMARY KEY，那么索引就在 (topic_id)将执行(id 将隐式添加到索引中)。

更新

如果您在 (topic_id, id) 和 (date, id) 上有索引，这个查询很可能会更有效:

有关性能详细信息，请参阅我博客中的这篇文章:

• Selecting last forum posts

此查询在 100,000 行示例数据上在 30 毫秒 内完成:

SELECT  id, name, last_reply,
        (
        SELECT  COUNT(*)
        FROM    wp_pod_tbl_forum fc
        WHERE   fc.topic_id = fl.topic_id
        ) AS replies
FROM    (
        SELECT  topic_id, date AS last_reply
        FROM    wp_pod_tbl_forum fo
        WHERE   id = (
                SELECT  id
                FROM    wp_pod_tbl_forum fp
                WHERE   fp.topic_id = fo.topic_id
                ORDER BY
                        fp.date DESC, fp.id DESC
                LIMIT 1
                )
                AND fo.topic_id <> 0
        ORDER BY
                fo.date DESC, fo.id DESC
        LIMIT 20
        ) fl
JOIN    wp_pod_tbl_forum ft
ON      ft.id = fl.topic_id
UNION ALL
SELECT  id, name, date, 0
FROM    wp_pod_tbl_forum t
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    wp_pod_tbl_forum r
        WHERE   r.topic_id = t.id
        )
        AND t.topic_id = 0
ORDER BY
       last_reply DESC, id DESC
LIMIT  20

此查询需要两个索引才能有效。

如果您的表是 InnoDB 并且 id 是一个PRIMARY KEY，那么您可以从上面的索引。