Am in the process of setting up and testing version 7 after upgrade from 6.5 to 7.5. We have quite a lot of posts from years past, but performance has never at all been an issue.
After creating a new user, UBB seems to go away and take *forever* running the following queries from the category/forum list screen:
# Query_time: 15 Lock_time: 0 Rows_sent: 1 Rows_examined: 52580
SELECT COUNT(p.POST_ID) AS POSTS, SUM(p.POST_IS_TOPIC) AS TOPICS, t.FORUM_ID FROM ubbt_POSTS as p, ubbt_TOPICS as t
(t.TOPIC_ID = p.TOPIC_ID) AND p.POST_IS_APPROVED = 1 AND (0 OR (t.FORUM_ID = 1 AND p.POST_POSTED_TIME > 0)) GROUP BY t.FORUM_ID;
It does this for every single forum.
The result:
"Generated in 90.780 seconds in which 90.730 seconds were spent on a total of 31 queries. Zlib compression enabled."
Clearly an index is missing somewhere, but I can't work out what needs to be added.
So:
1. Does the server REALLY need to be doing 31 queries when someone tries to pull up a list of the categories? That sounds way excessive and inefficient.
2. If yes, how can we get those queries down to a fraction of a second?
Thanks
Last edited by Snarf; 08/10/2010 10:38 AM.