Sulu: Warp 7 sir?
Kirk: It's too damn slow!...
-- Lyrics From: Star Trek Rhapsody by Weird Al
Here is the specific code that I use with my mods to the BBoard system in openacs3. What this does, is eliminate the "New Answers" page. This page is unreliable when using more than one machine to access the forums probably due to cookie changes. Also, the page often hides threads that I just didn't get around to read when I come back later.
My solution, implemented long ago, was to have just one page which shows the threads with the most recent listed first along with the name of the most recent poster.
Here is the code that I use. I have simplified it and removed the view so it is clear. The problem is that it can take 5 to 10 SECONDS to execute on our current bboard with about 18,000 messages.
The really slow part is the subselect: msg_id in (select... shown in red below. The subselect is used to get the most recent message in each thread. Every message in a thread has the same root_message_id and as well as the leading part of the sort_key (ex: 0004FF:01)
select now() from dual; SELECT bb.msg_id, bb.root_msg_id, bb.refers_to, bb.topic_id, bb.user_id AS poster_id, bb.one_line, bb.message, bb.html_p, bb.posting_time, bb.sort_key, bt.topic, u.first_names || ' ' || u.last_name AS name, u.email FROM bboard bb, bboard_topics bt, users u WHERE bb.user_id = u.user_id AND bb.topic_id = bt.topic_id AND bb.dele_ted ISNULL AND bt.topic_id = 11 and date_num_days(sysdate() - bb.posting_time) < 90 and bb.msg_id in (select max(bb.msg_id) from bboard group by bb.root_msg_id) order by bb.posting_time desc limit 1; select now() from dual;
I even added a 2 column index:
CREATE INDEX bboard_root_msg_msg_idx
ON bboard (root_msg_id, msg_id);
But this seems to have little effect.
I've racked my brains on how to get the most recent row from variable sized group AS FAST AS POSSIBLE.
As I understand it, it is best to do the processing in the database before getting the results. I had considered getting the whole thread out of the database and in tcl throw away the unneeded messages.
Another idea is to build a helper table that inserts or overwrites the most recent message in a forum "topic" every time a post is made. Then, when I want to show the who and title of the most recent message in a thread, I just join bboard,users, bboard_topics with the new helper table.