Optimize forumhome, the filburt1 Way

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

[high]* assassingod clicks install[/high]

[filburt1]

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

[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

[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
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 ). Either way though it's still a nice idea filburt.