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

XF 1.2 Webhost said there's a query that's taking 25 seconds per.

Discussion in 'Troubleshooting and Problems' started by LurkerLou, Dec 4, 2013.

  1. LurkerLou

    LurkerLou Active Member

    Anybody have a clue what the issue is?

    xf_post AS post

    WHERE post.thread_id = '2'

    AND post.post_date > '1383582088'

    AND (post.message_state IN ('visible'))

    ORDER BY post.position DESC, post.post_date DESC

    LIMIT 15;

    # User@Host: sqlmronline[sqlmronline] @ ps215118.dreamhost.com []

    # Query_time: 25.054649 Lock_time: 0.166211 Rows_sent: 0 Rows_examined: 10432
  2. Mike

    Mike XenForo Developer Staff Member

    It's used in a few different situations. In general, the thread_id, post_date index should be used to make it fairly efficient. Obviously this is related to a large thread if it's looking at 10,000 rows.

    However, in general, this shouldn't normally be taking that long, though I do see a couple potential tweaks I could make to the query. Given that I'm seeing DreamHost in the log, I assume it's a shared server so it's possible that MySQL simply isn't as optimized for your usage as it often could be (with a VPS/dedicated). At the least, you're contending with other sites so it's possible that there was high load on the server from them, which then applied here.

    On a final note, I'm assuming that the query time actually applies to this query. Normally the meta data is written above the actual query.
  3. LurkerLou

    LurkerLou Active Member

    @Mike, could it be a load from a very large thread?
  4. Jeremy

    Jeremy Well-Known Member

    Yes. Do you have any threads with roughly 10,000 posts?
  5. LurkerLou

    LurkerLou Active Member

    There are a number. I guess that's the main cause?
  6. Jeremy

    Jeremy Well-Known Member

  7. LurkerLou

    LurkerLou Active Member

    Thank you Jeremy.

Share This Page