Geek Answers Handbook

Optimize your MySQL query to avoid "Using where; Using temporary; Using filesort"

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

The database table has the following columns:

id name body date topic_id email

A topic has topic_id of "0" and the answers have topic_id of their parent section.

 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;

This table contains about 2100 items, and queries usually take 6 seconds. I added INDEX to the "topic_id" column, but that didn't help much. Are there any ways to expedite this request without significant restructuring?

EDIT : not working yet. I can't seem to give the examples below.

+6
performance optimization sql mysql
source share
2 answers
 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 MyISAM table or id not a PRIMARY KEY , you need to create a composite ondex on (topic_id, id) .

If your InnoDB table and id are PRIMARY KEY , the index only on (topic_id) will do ( id will be implicitly added to the index).

Update

This query is likely to be even more efficient if you have indexes on (topic_id, id) and (date, id) :

See this blog post for performance details:

This request completes in 30 ms in 100,000 sample strings:

 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 indices are necessary for the effective execution of this query.

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

+7
source share

You can break it down into a set of subqueries (as internal queries). I need a circuit to play, but if you

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

which may help speed it up a bit, maybe this is not even the best answer (errors may exist).

There are many ways to do this, but most importantly, when SQL tuning is to keep each set as small as possible before performing the operation.

+1
source share

More articles:


All Articles