查找mysql临时文件,优化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.

The DB table has the following columns:

id

name

body

date

topic_id

email

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;

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

If your table is MyISAM or id is not a PRIMARY KEY, you need to create a composite ondex on (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).

Update

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:

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.

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