XF 1.1 Forum List loading incredibly slowly.

J2A

Member
I am running a reasonably large Xenforo discussion board, (around 1,800,000 posts in total) and recently it has been taking an incredibly long time to load the forum list and the load on mySQL has increased dramatically (other pages are unaffected as far as I can tell)

Attempted to debug and this is what I've found, not sure of the best way to resolve the issue however.

The culprit query which seems to be running for a while is this one, it can take a few seconds to run and causes a lot of load on the database
Code:
SELECT post.post_id, post.user_id, thread.title, post.message, post.post_date FROM `xf_post` as post LEFT JOIN xf_thread as thread ON post.thread_id = thread.thread_id WHERE thread.discussion_state = 'visible' ORDER BY `post`.`post_date` DESC LIMIT 5

Running an explain on the query produces these results
Code:
EXPLAIN
SELECT post.post_id, post.user_id, thread.title, post.message, post.post_date FROM `xf_post` as post LEFT JOIN xf_thread as thread ON post.thread_id = thread.thread_id WHERE thread.discussion_state = 'visible' ORDER BY `post`.`post_date` DESC LIMIT 5

Q7WgZ.jpg


For some reason mySQL is not using the appropriate indexes and is forced to use a filesort, Removing the ORDER BY clause from the query removes the need for the filesort.

Any ideas on resolving this issue?

Thanks in advance.
 
Top Bottom