Ask a Question
Advertise on boostr.in
optimize mysql query to avoid using where using temporary using filesort using -'sql,mysql,performance,optimization'
optimize mysql query to avoid using where using temporary using filesort ¬†using -'sql,mysql,performance,optimization'
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:
A topic has the topic_id of "0", and replies have the topic_id of their parent topic.
¬†¬†¬†¬†t.id, t.name, MAX(COALESCE(r.date, t.date)) AS date, COUNT(r.id) AS replies
LEFT OUTER JOIN
¬†¬†¬†¬†wp_pod_tbl_forum r ON (r.topic_id = t.id)
¬†¬†¬†¬†t.topic_id = 0
¬†¬†¬†¬†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.
Sep 7, 2015
to add a comment.
Your name to display (optional):
Email me at this address if my answer is selected or commented on:
Email me if my answer is selected or commented on
Privacy: Your email address will only be used for sending these notifications.
To avoid this verification in future, please