Organizational Research By

Surprising Reserch Topic

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:

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.
    
asked Sep 7, 2015 by rajesh
0 votes
21 views



Related Hot Questions



Government Jobs Opening


...