I'm running a Joomla based Swedish community site for dancers, www.dansmaffian.se, which is growing quite quickly. We're currently about 250 concurrent users at the evenings.

I'm staring to run out of resources on my servers and the major bottleneck currently is the jb_last_subjects module which I've modified for my needs. I'm currently running on an old system based on Joomla 1.0.12 and JoomlaBoard 1.1.3 but am about to upgrade to the latest version of Fireboard, but like to discuss whether any improvments have been made regarding my specific problem.

The problem is that on the frontpage of the site I want to show a list of the 30 latest forumposts together with number showing how many replies each thread contains. However the jb (JoomlaBoard) message table doesn't seem to keep any record in the first post about how many replies it has gotten, so to find out how many replies each thread contains the whole datebase has to be searched for messages with the same subject, which is a real performance killer.

To make things even worse it didn't seem to be enough to do a GROUP BY on subject and ORDER BY time DESC, as the GROUPING is done before the reordering so timestamp returned by the group of records with the same subject is not the one with the latest timestamp, so the ordering of which thread has been commented lastly gets incorrect.

The only solution I found to that was to use a subquery returning ALL records in ORDER BY time DESC as an inner SQL query to the main SQL GROUP QUERY.

So basically my query looks like this, a little simplified though:

SELECT sb.subject, count(sb.id) as replies FROM
(SELECT * from #__sb_messages ORDER BY time DESC) AS sb)
GROUP BY sb.subject
ORDER BY sb.time DESC LIMIT 30

So, my question is, has the database structure been improved in newer versions of FireBoard so this can be done in a more effient way? Ideally I'de like the message table to contain two new columns, "replies" and "last_reply_date", where last_reply_date should contain the original post timestamp if no replies have been made.

This would allow the table to very efficiently indexed and searched for last posts. The only caveeat as I can see is that the delete post must also be made a little more intellegent, decreasing the replies-counter on the threadstarting post.

As usual when you post on forum you ge inspired to dig deeper into the problem yourself!

So after some googleing I found the tip to use MAX() instead to find the latest post which allowed me to get rid of the subquery. So now I got:

SELECT subject, count(id), max(time) as last FROM jos_sb_messages
GROUP BY thread
ORDER BY last desc limit 30;

This made the query take about 0.2 secs to complete instead of 10 seconds with the last query! Quite an improvement!!

But the question remains, wouldn't it be even smarter to store the accual number of replies in the record holding the first post in a thread, so we don't need the GROUP BY at all? Or has this already been changed in newer versions?

Regards Mathias

Mathias,

You bring up some very valid points. Over time the requirement for on the fly calculations has increased significantly. Nothing we can fix for 1.0.5 as it would require too much of a code re-write.

We are just about to redefine those datamodel elements for 2.0 as part of the ongoing development. The intention is to remove any such computations from the access queries, hence speed up processing significantly. Things like view counts, reply counts, page counts and other derived information needs to be hold on the thread level and updated with every new post.

As soon as we have more details we will share them with you and the community.

Alright, great to here it's on the roadmap atleast then.

So, until then is the SQL query I wrote in my second post the most efficient way to get last active thread with number replies list or do you know of a better way with the current database design?

Thanks for your reply anyway // Mathias

I would recommend you add a WHERE condition to limit the amount of messages being processed. Your query will always process ALL messages and then return the last 30. If you know that the last 30 are always well within a week, I would add a time check to limit the messages to the ones posted in the last 7 days( or whatever works for you). That way the performance should increase significantly.

I can also recommend

JRE Cache

(Look for it on the Joomla site)

Set up a 10 min cache for all guests. It will lower the database workload of your site significantly if you have a lot of non-logged in users

Well, the LATEST post in each thread in the top 30 is always well within a week, but often someone finds and old thread and gives it a reply. Wouldn't a WHERE clause then give false replies-counters as the posts older that a week wouldn't be counted, right?

isn't our "recent discussions" what you are looking for? if so you can take a look at rc1's codes. it should be similar with jb (though jb misses some indexes).

fxstein wrote:
I can also recommend

JRE Cache

(Look for it on the Joomla site)

Set up a 10 min cache for all guests. It will lower the database workload of your site significantly if you have a lot of non-logged in users

Have not tried this product. Do you think it will increase the performance of boj?

fxstein wrote:
I can also recommend

JRE Cache

(Look for it on the Joomla site)

Set up a 10 min cache for all guests. It will lower the database workload of your site significantly if you have a lot of non-logged in users

I will have a look at it. I've tried the build-in cache system in Joomla 1.0.12 but it fucked up comments made via Akocomment to articles. It made them appear as someone else had written them.

Since then I've turned of the global caching system and instead tried to implement my own cachesystem using PHP's CacheLite on the resource hogging modules. So this specific module (jb_last_subjects) I already keep a cached copy of the resulting HTML code, so I don't think an additional cacheing system will help there, but it might well be worth looking at for the overall performance anyway.

Unfortunally I don't have many guest users so I'm not sure a global cache system will help me much.

Btw, yesterday I also added AJAX support to the last-posts modules so they update dynamically every 5 seconds without refreshing the page. Really nice.

But still, executing the query every 5 seconds take a lot of resources.

I'll have a look at the "recent discussions" code from rc1. That seems to be exactly the same thing as I'm trying todo to see you have found a smarter way.

If I can't find any smart trick to make it substantially more efficient that now I'll probably upgrade to the latest Fireboard release and then implement a workaround by adding a new table containing each treadID, replies, last_reply_date and simply patch Fireboard to update that table on each forum post and delete. Shouldn't be many lines of code but should speed up the query very much.

BTW, thanks a lot for the GREAT work on Fireboard, it looks like some major improvments since the Joomlaboard release I'm currently running.