Optimize forumhome, the filburt1 Way

[filburt1]

This hack greatly reduces the number of queries on forumhome. On my test board, before:

Page generated in 1.81393003464 seconds with 30 queries,
spending 1.17402160168 doing MySQL queries and 0.639908432961 doing PHP things.

After:

Page generated in 1.37496697903 seconds with 20 queries,
spending 1.00117218494 doing MySQL queries and 0.373794794083 doing PHP things.

The very, very clever hacker can combine elements of this hack and Xenon's to reduce forumhome queries even more; specifically, his optimizations for a moderator cache and Who's Online.

There's also an added benefit: since the number of rows in all tables are cached and not just users, posts, and threads, you can use $counts[tablename] anywhere in forumhome and have the number of rows in tablename show up (for example: Mail Messages: $counts[mailmessage]).

Note that because some PM processing is now given to PHP instead of MySQL, the page load time might increase. It will depend on how many PMs the current user has, among other things. However, the PM handling eliminates two queries.

[corsacrazy]

hey if i sent u my index.php wood u b able 2 gib us a hand optimising ? my index.php is hacked up :(

i hav 26 queries on forum home

[Boofo]

Interesting idea. I don't have Xenon's hack installed, but I am using Tigga's Forumhome Stats cache. Care to add the moderator's cache and online users cache? ;)

[assassingod]

Nice filburt:)

* assassingod clicks install

[filburt1]

Sorry but you're not allowed to send people full vB files (not that I don't get them every now and again :p).

[Tigga]

Odd.. I tried installing this and it actually increased the page load time. I already have Xenon's hack and my Stats Cache installed, so I wouldn't really see the benefits for parts of this. It seems to be coming mostly from the PHP code that gets the private message info...

Before
Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND messageread=0 AND folderid=0
Time before: 0.19265496730804
Time after: 0.22409498691559

table type possible_keys key key_len ref rows Extra
privatemessage ref userid userid 4 const 1947 Using where

--------------------------------------------------------------------------------

Query: SELECT * FROM statscache
Time before: 0.2369509935379
Time after: 0.23781204223633

After
Query:
SELECT privatemessageid, messageread, dateline, folderid
FROM privatemessage
WHERE userid = 1
Time before: 0.28443694114685
Time after: 0.32564997673035

table type possible_keys key key_len ref rows Extra
privatemessage ref userid userid 4 const 1947 Using where

--------------------------------------------------------------------------------

Query: SELECT * FROM statscache
Time before: 0.50849199295044
Time after: 0.54340195655823

[Xenon]

nice hack filburt, i see some really nice things in it.
but the PM query, i think it would take longer if a user has more PM's stored, so i think here your way will increase the load of the system more than it saves...

edit: also the newest member part ist slower than the original way i think, here would help a cache (like on vb.org ;)) more i think

[filburt1]

True, it's a tradeoff: one query instead of three for PMs, but the processing is handed over to PHP instead.

I'm morally opposed to caches, they're a PITA to update :p

[Xenon]

i like caches if i can use them without to much problems ;)

yeah one query instead of three is better this way.
but i know from my first version where i had one query for pming it was much slower than the old three, when a user has had a lot pm's that's why i used my compromis of two queries ;)

nevertheless, i'll see if i can use some of your ideas here ;)

[Tigga]

Today at 02:07 PM Xenon said this in Post #7 (https://vborg.vbsupport.ru/showthread.php?postid=400672#post400672)
but the PM query, i think it would take longer if a user has more PM's stored, so i think here your way will increase the load of the system more than it saves...
Good point. I have over 2,300 PM's saved right now. When I logged in with a test account though it did seem to speed the generation time up by just a little. Because of the other hacks I have installed though it only saves one query and the page generation time doesn't seem to be enough to warrant installing it (plus I don't want to slow the generation time down for myself :p). Either way though it's still a nice idea filburt. :)

[Dean C]

The cache-meisters :)

I'd love to see showthread.php's queries reduced :)

- miSt

[Boofo]

Tigga and xenon, which parts would be ok to use then? ;)

[Xenon]

@Boofo: you can use everything, the pm thing i'd suggest to use mine, but the other stats are good by filburt.
it depends also a bit on your board, what you have optimized already ;)

[Boofo]

Right now, I have Tigga's Forumhome Statscache installed. I had your hack installed but had to remove it when I couldn't get the browsers hack working with it. ;)

[Xenon]

boofo that was version 1 of my hack, version 2 has no problems with any hacks :)

you should use some parts of mine and others by filburts to get an optimum :)

[Boofo]

Your version 2 works with the countbrowsers on forumhome? I didn't know that. I will have to use it now. Will it work ok with Tigga's hack or do I need to uninstall that one first and then re-install it after I install yours?

[Chris M]

Nice filburt!:)

Got my forumhome (on a new site) down from 22 to 16:)

Satan

[Alien]

I have Xenon's excellent hack installed. No other forumhome optimizations are installed.

What parts of this specifically should I install that will benefit me? I got myself down to 19 queries and would love to move it down further! :D I'd also like to skip out on the PM optimization, I'd just like to use whatever else that isn't a part of Xenon's hack.

-Jason

[nuno]

Nice hack.
Thanks filburt.

[xs1]

thx installed the hacksucessfully

from 26 to 22

[sonic3d]

nice. from 22 to 17.

l8er
sonic

[Erwin]

17? Very sweet. I already have 22 on mine with no optimization except my own, but I don't use caching. Just modifying code, and removing things I don't need, like the moderator query (did you know that you can take that away if you don't have a moderator column?).

[Boofo]

But if you have the Miderators listed like they do here on vb.org (Moderated By: ), you still need it right?

[Xenon]

for now, yes, but i'll remove it soon ;)

that's where i like my hack ^^

[Boofo]

But you will still have the "Moderated By:", right?

[limey]

I went from 20 to 13, but the members, posts, and birthdays act wacky so I'm putting it back :(

[limey]

I went from 20 to 13, but the members, posts, and birthdays act wacky so I'm putting it back :(

[base]

Yesterday at 11:39 PM Erwin said this in Post #23 (https://vborg.vbsupport.ru/showthread.php?postid=401295#post401295)
17? Very sweet. I already have 22 on mine with no optimization except my own, but I don't use caching. Just modifying code, and removing things I don't need, like the moderator query (did you know that you can take that away if you don't have a moderator column?).


how would i accomplish that? would it simply be a case of removing
$forummoderators=$DB_site->query('SELECT user.userid,user.username,moderator.forumid
FROM moderator
LEFT JOIN user
ON (moderator.userid=user.userid)
ORDER BY user.username');
while ($moderator=$DB_site->fetch_array($forummoderators)) {
$imodcache["$moderator[forumid]"][] = $moderator;
$mod["$moderator[userid]"] = 1;
}
$DB_site->free_result($forummoderators);
unset($moderator);
from the index.php file, or are there other bits that i've missed? i already have removed the mod columns on forum home etc etc

many thanks in advance :)

[Xenon]

just remove that part yes :)

[base]

thank you very muchly, one down, now at 27. i'm thinking about using filburts mods to lower it some more still.

[Erwin]

Today at 08:43 AM Boofo said this in Post #24 (https://vborg.vbsupport.ru/showthread.php?postid=401299#post401299)
But if you have the Miderators listed like they do here on vb.org (Moderated By: ), you still need it right?

???

You just edit the template... is that what you mean?

[Tubbie]

another nice hack from u!

greetz Sebi

*install*

[Boofo]

Today at 03:18 AM Erwin said this in Post #31 (https://vborg.vbsupport.ru/showthread.php?postid=401464#post401464)
???

You just edit the template... is that what you mean?

Do I still need the moderator cacheing code in the index.php for the Moderated By: ?

[Xenon]

If you want it like vb.org you must cache the mods in the forumtable (my hack) or use the standart method ;)

[Alien]

Anyone have any ideas on this? :)

https://vborg.vbsupport.ru/showthread.php?postid=400746#post400746

[Xenon]

as i already said, you can use every line of filburts hack which doesn't modify the same codeblocks than my hacks and they'll work together perfectly :)

[Alien]

Okay just wanted to be extra careful not to break anything. :)

I'll give it a whirl!

[gmarik]

Filburt - it's fantastic!

[Hobbes]

excellence!!!!! my forumhome is loading great, along with showthread!!! YAY!!! ....sadly, the forumhome stats hack by TECK ( i think :o ) doesnt work on my forums :( *cries*

[ImportPassion]

mine is pretty good. I have 305 PM's.

[Alien]

Maybe someone can help me with a little step by step in getting this hack and the one by Xenon to work perfectly together? I attempted to merge the two together myself and it hasn't worked with multiple attempts.

Has anyone done this properly and can say specifically what to leave out of one hack or another to get them to play happily in the playground together? :D

Note: I have Xenon's hack installed already, and want the best parts of this hack to work nicely with it! Thanks for any help you can provide. This is the last thing I have to get past to go live with v3 of my site.

[Alien]

Anyone out there anymore? :D

[Alien]

Can anyone give a step by step on how to accomplish this?

[Oblivion Knight]

Sweet deal.. Brought my queries down from 23/24 on forumhome to 19/20, and everything appears to still be running ok. The load appears to slightly depend more on MySQL than PHP than it did previously (before: 80% PHP, 20% MySQL - after: 75% PHP, 25% MySQL), but that's nothing major.

Nice optimizing there filburt.. :)

[Alien]

I'd still like to get this to cooperate nicely with Xenon's hack, can anyone help with my post above? :D

[Alien]

Can anyone help with a small step by step in making these 2 work perfectly together? :)

[msimplay]

old
Page generated in 0.13271797 seconds (85.62% PHP - 14.38% MySQL) with 37 queries.
[Debug Mode OFF] [PHP v4.3.2] [GZIP enabled - level 1] [Server Load: 0.54 ? 0.61 : 0.92]

New
Page generated in 0.1300003 seconds (72.34% PHP - 27.66% MySQL) with 32 queries.
[Debug Mode OFF] [PHP v4.3.2] [GZIP enabled - level 1] [Server Load: 0.77 ? 0.63 : 0.90]

Basicly 37 down to 32 queries the forums gone blazing fast
on a really hacked forum ie 80+ hacks

erm wondering now can i still add xenons hack :p

but am wondering why server load has increased :s
and its going faster

[msimplay]

08-05-03 at 05:15 PM Alien said this in Post #46 (https://vborg.vbsupport.ru/showthread.php?postid=423439#post423439)
Can anyone help with a small step by step in making these 2 work perfectly together? :)


this file should work with filberts Optimisation

[Alien]

Unless you edited that file, I already have Xenon's hack installed as it was out first. I want to add this one for the additional 1-2 queries more that I can save but need a step by step on how to do this and I'm surprised noone has followed up on this.

It's not a simple matter (as I was hoping) of just modifying stuff in the usual way (and just not replacing one thing that both modify etc.). It seems more complicated than that when i started finding/replacing stuff so I quit it.

Can anyone help with a simple STEP-BY-STEP on this?

[msimplay]

i'm sorry i did in reverse to u i added Filberts hack first then i added Xenons hack afterwards like that

[Alien]

Not a problem.. I suppose if noone can help me, I could reverse all the steps in Xenon's hack and do filburt's first if that worked for you but I would prefer not to...

filburt1? Xenon? Help! :D

[msimplay]

lol yeh i know what u mean i really dont like hacking files but i enjoy the results
lol i added so many hacks that my forum homepage had too many queries

so least its not too bad now :D

ps i like your forum very nice erm wondering wha it would be like for 56k users :P

cuz i know my forum is a bit graphic heavy aswell

[MaDCaT75]

This hack seems to have made my moderator list dissapear :cry:

[NightWalk8r]

great hack!!!!

went from 18 to 15 queries on my index page :)

G/J

BTW..i install Xenon's hack after i got install this and it went down even more.....from 15 to 12 :D

[futureal]

I have noticed that using the SHOW TABLE STATUS query in this manner produces different results for threads, posts and users that if you do it the standard way.

For example, doing:

SELECT COUNT(*) AS count FROM post;

Produces a different result for me than doing:

SHOW TABLE STATUS;

And looking at the "Rows" column.

I am curious as to why this is. Anybody know?

[Fi_InCogNiTo]

Nice ^__^

Page generated in 0.44381301 seconds (89.90% PHP - 10.10% MySQL) with 18 queries.

Was 23 queries :)

[Set]

Hi @ll
This modification works with version 2.3.4?

thanks