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....

[N9ne]

I already did Xenon . PM Query mod I did about 4 months ago, and the thread/post counter I PMed you about and you helped me out on that too...

I have another board though, and it only has one, yes ONE! hack on forumhome, the change style drop down menu, so what do I need to do to get this to work with your mega-query reducer?

[Xenon]

well, the styledropdown hack should work with my hack without any problems as far as i know the hack

[Schorsch]

Hi Xenon,

I got a very rare error, it suddenly appears and it suddenly disappears (after pressing F5 in IE6):

Buffer Error

In line 487 is your piece of code:

if($cacheupdate) {
  $forumbits=makeforumbit(intval($forumid), 1, $permissions);
  if ($bbuserinfo['userid'] > 0 or ($ourtimenow - $bbuserinfo['lastvisit']) < $cookietimeout) {
    // remove old cache from user and those which are older than 5 hours
    $DB_site->query('DELETE FROM cache_forumhome WHERE userid=' . $bbuserinfo[userid] . iif($numbersmembers['lastpost']>(time()-3600*5), ' OR lastpost<'.(time()-3600*5),''));
    $DB_site->query('INSERT INTO cache_forumhome (userid,lastpost,lastupdate,postcount,styleid,forumbits) VALUES (' . $bbuserinfo[userid] . ',' . $numbersmembers[lastpost] . ',' . time() . ',' . $counters[posts] . ',' . $bbuserinfo[styleid] . ',"' . addslashes(gzdeflate($forumbits,9)) . '")');	
  }
} else {
  $forumbits=gzinflate($forumhomecache['forumbits']);    // <<<< LINE 487
}

I have 2 members who reported me this error, and 5 minutes ago I got the same error (for the first time) for about 1 minute.

Do you have any advice ?

thanks,
Schorsch

[squawell]

HI Xenon......

when i installed this but i have a problem

i cant logout.....and just only userid=1 cant....why??

so i replace original index.php file and it work.....

so Xenon what cause that??

[Xenon]

@Schorsch: well, it's a problem with your server, maybe the buffer is to small or something like that.

it's nothing i could repair ...

@Squawell: hmm, couldn't be normally the login/out feature are not touched by my hack.
Also it works perfect for all.
maybe you've changed the index on a piece of code where you shouldn't..

collidation with another hack you've installed propably?

[squawell]

Originally posted by Xenon
@Squawell: hmm, couldn't be normally the login/out feature are not touched by my hack.
Also it works perfect for all.
maybe you've changed the index on a piece of code where you shouldn't..

collidation with another hack you've installed propably?

in fact...it not just effect the login function.. when i add or edit the new forum the main page also didnt show up too(the change part).....so strange...it only happen to userid=1 user.....i try another user account its normal and work perfect.....so that make me confuse what cause that??

by the way i dont change anything in index.php i use the original index.php......

[Xenon]

maybe your cookie is corrupted.
i know it can't happen because the part of code is not affected by my hack, at least not if you've done everything correct.

the last thing is normal, whenever you add or edit a forum on forumhome or add a moderator or something like that, it will just change after the cache has been updated, so whenever one new post has been made.

[N9ne]

I had a problem with this hack, when we were editing some templates for forumhome, they weren't updating on forumhome, so you can't see the changes, and when someone could see the changes, someone else couldn't...all in all I couldn't risk any problems and I just uninstalled the caching part of the hack..

[Xenon]

@N9ne: It's not a problem.
all you have to do after editing a template on forumhome is to make a new post and then all caches would be updatet...

you could also edit templates.php to manually delete all cached versions whenever you make a templateedit.

As said, it's not a problem, it's just how caching works without to rewrite hundreds of files

[pwr_sneak]

i just read your instructions to see if this hack would work for my forum.
but this query is much slower then the original ones:

PHP Code:

SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers 


you replaced three fast queries which reads the values from index by one slow query which crawls through the whole datafile. i did a quick test on my privatemessage table (1.2M messages), your query took 2.2 seconds to complete, the original ones took 0.2 seconds all together

but the rest of this cache looks good

[Boofo]

Is there any way to make the one query faster?

[pwr_sneak]

I've updated my last post due to an cut'n'paste error...

Originally posted by Boofo
Is there any way to make the one query faster?

you have to run the original 3 queries to make it faster.
it's a good idea trying to decrease the number of queries, but it won't help anything if you replace some small and fast queries with one big and slow query.

[nuno]

Originally posted by pwr_sneak
I've updated my last post due to an cut'n'paste error...


you have to run the original 3 queries to make it faster.
it's a good idea trying to decrease the number of queries, but it won't help anything if you replace some small and fast queries with one big and slow query.

Read this thread
https://vborg.vbsupport.ru/showthrea...threadid=38471
Also read this post made by Chen
https://vborg.vbsupport.ru/showthrea...638#post254638
Also read Matt's post <- This guy is a db Master BTW.
https://vborg.vbsupport.ru/showthrea...690#post254690

[pwr_sneak]

Originally posted by nuno

Read this thread
https://vborg.vbsupport.ru/showthrea...threadid=38471
Also read this post made by Chen
https://vborg.vbsupport.ru/showthrea...638#post254638
Also read Matt's post <- This guy is a db Master BTW.
https://vborg.vbsupport.ru/showthrea...690#post254690

Matt runs Sybase instead of MySQL, you can't compare this.
MySQL optimizes count(*) queries because it keeps an extra counter in its index-files when using MyISAM Tables. The Index should be read from RAM on heavy-used tables. This kind of queries as well as MAX(indexed-column) can be answered without I/O which makes it really fast.

this sum(...,1,0) trick has to read every row from the table or just every entry in its index if its condition matches from index. so this is always slower than reading three single values with three single queries.

[nuno]

Originally posted by pwr_sneak

Matt runs Sybase instead of MySQL, you can't compare this.
MySQL optimizes count(*) queries because it keeps an extra counter in its index-files when using MyISAM Tables. The Index should be read from RAM on heavy-used tables. This kind of queries as well as MAX(indexed-column) can be answered without I/O which makes it really fast.

this sum(...,1,0) trick has to read every row from the table or just every entry in its index if its condition matches from index. so this is always slower than reading three single values with three single queries.

Originally posted by FireFly
I did that Matt.

PHP Code:

  $allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
                                SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
                                SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
                                FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

  $newpm['messages']=$allpm['newpm'];
  $unreadpm['messages']=$allpm['unreadpm']; 


Well I copied it from somewhere, don't really remember right now. But the problem is the query is slower for users with lots and lots of PM's (like myself), but considering most people don't I thought it would be wiser to just use that one query.

Chen already said one query is slower, however, this only occurs when you have a considerable amount of PM's stored.
So, i think your assumption is not correct.
It is faster for users with few pm's.