I help doing admin tasks in a relatively big board (15M posts) running phpBB 3.0.5 and MySQL, and these days we were having some load problems. I am no expert in databases, so if something that I am about to say is wrong, please do tell me.
- Code: Select all
FROM topics t
WHERE t.forum_id = (...)
AND t.topic_type IN (0, 1)
AND t.topic_deleted = 0
AND t.topic_approved = 1
ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
The server sometimes was struggling with dozens of queries like that. So looking at the PROCESSLIST we saw that the hard part for this query was the "Sorting result" phase. With an explain, we could see that it was spending lots of time with a filesort process. So we added an index on 'topic_type' and 'topic_last_post_time' fields. But MySQL was ignoring the index (I've read this is a common bug with the MySQL optimizer...), so we added a FORCE INDEX(name_of_the_new_index). The 'Using filesort' parte disappeared from the EXPLAIN, and the load went down by orders of magnitude!
I am writing just if it's useful for somebody, or if it's useful for the development of the next version of phpBB.
PS: right now we are in process of tuning more queries and indexes, I'll keep you updated