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

Discussion in 'Troubleshooting and Problems' started by J2A, Dec 1, 2011.

  1. J2A

    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
    [​IMG]

    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.
     
  2. Mike

    Mike XenForo Developer Staff Member

    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.
     
  3. J2A

    J2A Member

    Any idea of the best course of action to hunt down the culprit addon?
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    Do you have a latest threads/posts add-on, or anything of that sort?
     
  5. Deebs

    Deebs Well-Known Member

    Disable all addons, enable each one one at a time and see if the performance suffers?
     
  6. steel_curtain

    steel_curtain Well-Known Member

    Do you have AzuCloud - Long Tail SEO Cloud installed?
     
  7. J2A

    J2A Member

    Turns out that the Addon was called "Last Posts"
    Seems to have resolved the issue after disabling it.

    Thanks for the help guys.
     
  8. Deebs

    Deebs Well-Known Member

    You could add an index to help the WHERE clause.
     

Share This Page