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

LurkerLou

Active member
#1
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 [205.196.209.148]

# Query_time: 25.054649 Lock_time: 0.166211 Rows_sent: 0 Rows_examined: 10432
 

Mike

XenForo developer
Staff member
#2
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.