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

[LurkerLou]

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]

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.

 

[LurkerLou]

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

 

[Jeremy]

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

 

[LurkerLou]

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

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

 

[Jeremy]

Yes.

 

[LurkerLou]

Thank you Jeremy.