• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.1 Forum List loading incredibly slowly.

#1
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


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.
 

Mike

XenForo developer
Staff member
#2
That's from an add-on - it's unlikely there's an index on post.post_date so it's going to be a full table scan.