优化MySQL查询，以避免“在何处使用"；使用临时的；使用文件排序" [英] Optimize MySQL query to avoid "Using where; Using temporary; Using filesort"

问题描述

我使用MySQL为自己的网站建立了一个自定义论坛.列表页面实质上是一个包含以下各列的表:主题，最近更新和#个回复.

I built a custom forum for my site using MySQL. The listing page is essentially a table with the following columns: Topic, Last Updated, and # Replies.

DB表具有以下列:

id
name
body
date
topic_id
email

主题的topic_id为"0"，回复的父主题的topic_id.

A topic has the topic_id of "0", and replies have the topic_id of their parent topic.

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，但这并没有太大帮助.是否有任何方法可以在不进行重大重组的情况下加快此查询的速度?

There are about 2,100 total items in this table, and queries usually take a whopping 6 seconds. I added an INDEX to the "topic_id" column, but that didn't help much. Are there any ways of speeding up this query w/out doing significant restructuring?

编辑:目前还不能正常工作.我似乎无法使下面的示例正常工作.

EDIT: not quite working yet. I can't seem to get the examples below to work properly.

推荐答案

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)上创建复合ondex.

If your table is MyISAM or id is not a PRIMARY KEY, you need to create a composite ondex on (topic_id, id).

如果您的表是InnoDB，而id是PRIMARY KEY，则仅在(topic_id)上的索引即可(id将隐式添加到索引中).

If your table is InnoDB and id is a PRIMARY KEY, an index just on (topic_id) will do (id will be implicitly added to the index).

更新

如果您在(topic_id, id)和(date, id)上具有索引，则该查询很有可能会更加高效:

This query will most probably be even more efficient, provided that you have indexes on (topic_id, id) and (date, id):

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

See this article in my blog for performance details:

• 选择最新论坛帖子

此查询在30 ms行样本数据上的30 ms中完成:

This query completes in 30 ms on a 100,000 rows sample data:

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

要使该查询有效，两个索引都是必需的.

Both indexes are required for this query to be efficient.

如果您的表是InnoDB 并且，而id是PRIMARY KEY，则可以从上面的indexes中省略ID.

If your table is InnoDB and id is a PRIMARY KEY, then you can omit id from the indexes above.

这篇关于优化MySQL查询，以避免“在何处使用"；使用临时的；使用文件排序"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！