Speeding up Beast Forum
(n.b., the version of Beast we use may not be the most current. This post may not reflect what's in trunk.)
One of our clients from work uses Beast forum. Unfortunately last week it kind of died. Mongrel's logs were complaining that it couldn't allocate memory, the index (ForumsController#index) was taking upwards of 12 seconds to load...
The problem was SQL. Beast is very greedy when it displays the front page; it does a forums.posts.last.created_at to get the timestamp for when the last post was made. This is done for every top-level forum and it will destroy your site.
At about 21:30ish the previous night I disabled the site because my brain was mush and I couldn't focus enough to diagnose the problem to say nothing of finding a solution. The next day when I got to the office I spent the first 90 minutes of the day diagnosing and fixing the problem. I found that I could find the relevant data with a GROUP BY and ORDER BY with one query for all forums... Except the times were the FIRST post and not the LAST post.
So I spent the next two hours figuring out why MySQL didn't want to ORDER BY and formulating a query that was both quick, returned data for every forum and worked. This is the ugly thing that resulted:
-
-- moderator_only is assigned from clean data in the controller; it is safe.
-
SELECT posts.id, posts.created_at, posts.forum_id, posts.topic_id, users.login
-
FROM posts
-
INNER JOIN (SELECT MAX(id) AS id FROM posts GROUP BY forum_id) ids ON
-
posts.id = ids.id
-
LEFT OUTER JOIN users
-
ON users.id = posts.user_id
-
LEFT OUTER JOIN forums
-
ON forums.id = posts.forum_id
-
WHERE
-
(forums.moderator_only = 0 OR forums.moderator_only = #{moderator_only}) AND forums.parent_id is null
-
ORDER BY posts.created_at
This is fed into:
-
last_posts = Post.find_by_sql "...."
-
@last_posts = last_posts.group_by &:forum_id
In the view we just substitute forum.posts.last with @last_posts[forum.id].first in all instances. This may have to bet tweaked for your setup because I'm not sure where Beast ends and client-specific stuff begins.
You see there's some parent_id things going on in the SQL. At work we made Beast forum nested. We do plan to make these changes available for others to use but it's just a matter of finding time to get rid of the client-specific modifications and merging to trunk.
This is one of the things that I'm proud about: the SQL query was a pain!
