Optimizing Forumhome

Ok, what this Hack does is it reduces the ammount of queries on forumhome by caching the moderatorlist and by optimizing most of the queries used on index.php


I'd really appreciate getting feedback how it reduces serverload on greater boards

On an unhacked vb index.php (pm enabled accesmask disabled) it reduces the ammount of queries from 19 to 15
my experiments have shown, that the queries are faster than the normal ones, too....

[NTLDR]

Thanks for releasing Xenon, I'm going to be sure to test this one

[Xenon]

i hope i'll get good results by you NTLDR

[Dean C]

Yea i'll test this one out - sounds awesome :P

[Chris M]

Sounds good

Will install

Satan

[JulianD]

I'm going to test it for my new site! Thanks Xenon!

[Xenon]

i have to thank everyone who test it to bring back some feedback to me

if it works as it should i'll create other hack like that (have some ideas )

@All: my PM-solution worked wrong (damn) the file is updated with the well known pm-query, you should use this instead

[Lanigironu]

This is great!

My queries used to be at 48, and now dropped to 38.

I know this is extremely high, so are there any ways to lower it any more?

[Boofo]

Xenon, I did a re-fresh with 21 queries, it jumped to 23 and on another refresh, it went down to 17. I figure it was the cache kicking it. Its there a way to add this:

$totalviews=number_format($countthreads['views']);

to the query you have to make one less query if we are using this?

Also, the code for this:

$numbersmembers=$DB_site->query_first('SELECT COUNT(*) AS users,MAX(userid) AS max FROM user');
$numbermembers=number_format($numbersmembers['users']);

// get total posts
$countposts=$DB_site->query_first('SELECT COUNT(*) AS posts FROM post');
$totalposts=number_format($countposts['posts']);

$countthreads=$DB_site->query_first('SELECT COUNT(*) AS threads FROM thread');

must be a little different in 2.2.9.

[Xenon]

Uploaded new version with small bugfix
be sure you also make the table change to have lastupdate in it


Boofo: Well, yes it can be changed, if you use the threadtable instead of the forum-table, but the forumtable is faster, and on an unhacked vb229 i've used the optimized version

once it's out of the beta i can make it for you or post it here ok?

must be a little different in 2.2.9

hmm, no i've compared it with an unhacked vb229 file, maybe your fle is hacked...

Xenon, I did a re-fresh with 21 queries, it jumped to 23 and on another refresh, it went down to 17. I figure it was the cache kicking it.

Yes there are 3 extra queries compared to an normal forumhome call when the cache is updated, but at least 3 less to a normal one if read out from the cache.
But the additional queries are very fast from what i know compared to the others

[Boofo]

I don't want it changed if it can be incorporate it into what you already have. It's just that i don't have any threadviews anymore.

I haven't changed anything there (that I can recall). Must be an extra space or something in my file.

[Xenon]

of course, because the threadviewpart was a hack wasn't it?

instead of this code:

PHP Code:

$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, SUM(threadcount) AS threads FROM forum WHERE parentid=-1');
$totalposts=number_format($counters['posts']);
$totalthreads=number_format($counters['threads']); 


use this one:

PHP Code:

$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, COUNT(*) AS threads, SUM(views) AS views FROM thread');
$totalposts=number_format($counters['posts']+$counters['threads']);
$totalthreads=number_format($counters['threads']);
$totalviews=number_format($counters['views']); 


so it should work and you have your views again

[Boofo]

Thank you, sir. And I'm still at 17 queries. You are the Man!

I need to ask, though, is it normal to have this hack add 2 queries until the refresh? I mean, it is supposed to do that, right?

By the way, the threadviews was more of a line of code I got from Chen than a hack.

[Xenon]

yes it is normal
as said above the two extra queries are fast one's
but i can't exactly say the performance power, that's why it's here in beta

[Boofo]

Ok, well, I have noticed the page loads a lot quicker and smoother. So, it must be doing something. But, then, my board doesn't have very many users either, so I can't tell on the server load part of it. Although my server load stats in Microstats are showing 0.09, 0.05, 0.09 right now, if that means anything at all.