In our case, this accelerated the deployment of the Etomite Open Source CMS on our Web site that includes the phpBB forum. As I had already built some additional Database infrastructure into Etomite using php (which Etomite calls Snippets), it was relatively easy to add some more to process this (and any other) SQL query.
I have successfully used the following SQL to display an ordered list of the most recent topics for which posts were made, across all Forums, with a couple of nice touchs: only lists a topic once, even if multiple posts have been made; and only shows topics that "anyone" can View or Read (auth_view and auth_read).
Code: Select all
SELECT forum_name, p1.forum_id AS forum_id, topic_title, p1.post_id, username, TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(p1.post_time))) DIV 3600 AS hours, MINUTE(TIMEDIFF(NOW(), FROM_UNIXTIME(p1.post_time))) AS minutes
FROM phpbb_topics, phpbb_users, phpbb_forums, phpbb_posts AS p1
JOIN (
SELECT topic_id, MAX(post_time) AS post_time FROM phpbb_posts WHERE post_time > UNIX_TIMESTAMP(SUBTIME(NOW(), '72:00:00')) GROUP BY topic_id)
AS p2 ON p1.topic_id = p2.topic_id AND p1.post_time = p2.post_time
WHERE p1.post_time > UNIX_TIMESTAMP(SUBTIME(NOW(), '72:00:00')) && p1.topic_id = phpbb_topics.topic_id && poster_id = user_id && p1.forum_id = phpbb_forums.forum_id && auth_view = 0 && auth_read = 0
ORDER BY p1.post_time desc LIMIT 0 , 40
Performance: the two 72:00:00 WHERE clauses are not redundant. Without them, Performance is unacceptable on all but the smallest phpBB databases.
I'll edit this post when I move it, but, for now, here is a live working version of all this: http://www.edmontonbroadcastersclub.com/index.php?id=94