Thread view optimizations

[Zzed]

This is a little hack that I did to improve the speed at which threads are
loading in my forums. I am experiencing very high server loads due to the
limited hardware on the server and due to the high post counts in my forums.
This hack has tremendously sped up showthread.php.

In order to view an entire thread, showthread.php makes 3 queries into the
post table. I have managed to divert one of these queries to the thread table,
and have managed to combine the remaining 2 queries into a single query.

The thread table is usually 10%-15% of the post table in terms of count, and
it is about 5% of the post table in terms of its physical size. Using the
thread table for one of the queries will definitely improve things.

This hack requires no template or table changes. It is just 3 simple code
changes made to the source code.

[Hobbes]

looks nice :D will instlal

* Hobbes clicks instlal when he finishes

[Velocd]

How about some benchmark statistics, by curiousity? ;)

What was the loading time before, and what is it now?
(that is, if you have Teck's microstats installed)

[mx3]

I'll install this...speed is good. :)

*mx3 clicks install

[jdmuniverse]

I'd like to see some stats too :)

[mx3]

Loading a bit faster for me :)

[Pikok]

Yesterday at 11:25 PM Velocd said this in Post #3 (https://vborg.vbsupport.ru/showthread.php?postid=421659#post421659)
How about some benchmark statistics, by curiousity? ;)

What was the loading time before, and what is it now?
(that is, if you have Teck's microstats installed)
Before:
Page generated in 1.02129197 seconds (86.35% PHP - 13.65% MySQL) with 28 queries. [GZIP enabled - level 1]After:
Page generated in 1.00562406 seconds (86.31% PHP - 13.69% MySQL) with 27 queries. [GZIP enabled - level 1]I would think that the savings would be more noticeable for larger threads with numerous replies. The above stats are for a thread with only 2 replies (3 posts total). I think the larger the thread, the more noticeable the increase will be. But, even with a small thread I noticed an increase in speed. It does work! ;) Thanks Zzed!

Perhaps someone with somewhat larger threads could post some stats..

[sonic3d]

Page generated in 0.61381495 seconds (81.33% PHP - 18.67% MySQL) with 25 queries.
Page generated in 0.41559005 seconds (80.16% PHP - 19.84% MySQL) with 24 queries.

it loads fast i suppose.

l8er
sonic

[Oblivion Knight]

Always nice to drop a query when installing a mod instead of gain one..

Nice work :)

[Dolby]

So far its working nice. Why didnt vBulletin do this? Is there some sort of draw back?

[Darkwaltz]

Nice, easy to install and speeds up my pages a lot

*install*

[MrNase]

:bunny: wow! loads a lot faster, thx :)

clicking install....

[pwr_sneak]

Today at 08:36 AM Dolby said this in Post #10 (https://vborg.vbsupport.ru/showthread.php?postid=421737#post421737)
So far its working nice. Why didnt vBulletin do this? Is there some sort of draw back?
they didn't implement this because it would not work for forums which moderate new posts.
but you could use $forum['moderatenew'] to decide which version you wanted to use.

if ($forum['moderatenew']) {
//use old queries
}
else {
//use faster queries
}

[ed2k_2]

Installed without any problem. Threads load more faster!
Thanks Zzed!

[Dean C]

Same here :)!

[corsacrazy]

Today at 05:21 PM Mist said this in Post #15 (https://vborg.vbsupport.ru/showthread.php?postid=421821#post421821)
Same here :)!


where to ? :ermm:

[Dean C]

My forums are private :)

[oldgamesnu]

Before: in 0.24841595
After: 0.20439696

[fury]

07-30-03 at 07:04 AM pwr_sneak said this in Post #13 (https://vborg.vbsupport.ru/showthread.php?postid=421772#post421772)
they didn't implement this because it would not work for forums which moderate new posts.
but you could use $forum['moderatenew'] to decide which version you wanted to use.

if ($forum['moderatenew']) {
//use old queries
}
else {
//use faster queries
}


Why not just change this line...

$postids.= "post.threadid='$threadid'";

to this:

$postids.= "post.threadid='$threadid' AND post.visible='1'";

It might slow it down a tiny bit more but it works with moderated forums...

[fury]

BTW, this (after you've applied the mod)
$postscount=$DB_site->query_first("SELECT replycount AS posts FROM thread WHERE threadid='$threadid'");
$postscount[posts]++;
$totalposts=$postscount[posts];
can be replaced, to save yet another query, with this: $totalposts = $thread['replycount'] + 1; since the thread info is already fetched earlier in the file.

[pwr_sneak]

Today at 07:18 PM fury said this in Post #19 (https://vborg.vbsupport.ru/showthread.php?postid=422871#post422871)
Why not just change this line...

$postids.= "post.threadid='$threadid'";

to this:

$postids.= "post.threadid='$threadid' AND post.visible='1'";

It might slow it down a tiny bit more but it works with moderated forums...
for moderated forums you'll need the other query ($postscount = $DB_site->query_first("SELECT COUNT(*)...) too, to get the right pagecount and page navigation.
this hack speeds thread-display because it will not have to crawl the whole post-table to check the "visible" column, it just uses the indexed "thread" column which is alot faster. this is not "a tiny bit" this is MUCH faster on big forums whith huge threads.
I've installed it on my 2 million post forum and it runs like a charm now.

[fury]

Unfortunately I don't have a forum with 2 million posts to check it on. http://smilies.xibase.com/shrug.gif Why not index the visible column then? Since you have a multi-GB post table, what's an extra few dozen megs gonna hurt?

As for the post count, $thread['replycount'] is not incremented for posts that are placed in the moderation queue. it is only updated when they are taken out of the queue. That's the whole point of the reply count field, if there were more replies showing in the count on forumdisplay than were visible in the thread, people would start to wonder.

[cirisme]

I like this hack and it definately improved the speed, but we have a moderated users hack and this causes those posts to show up anyway, so I had to uninstall it.

Definately a great concept though :)

[romeshomey]

Nice.. Thanks

[Alien]

fury's suggestion for reducing yet another query, has this been independently confirmed as well before I play with it. :D

[Oblivion Knight]

Today at 05:24 PM Alien said this in Post #25 (https://vborg.vbsupport.ru/showthread.php?postid=423156#post423156)
fury's suggestion for reducing yet another query, has this been independently confirmed as well before I play with it. :D I haven't tried it yet.. Waiting for confirmation, the same as you ;)

[Alien]

Can this optimization work with this hack below?

https://vborg.vbsupport.ru/showthread.php?s=&threadid=36416

These lines (that you ask me to replace) are different in my showthread.php:


$postscount=$DB_site->query_first("SELECT COUNT(*) AS posts FROM post WHERE post.threadid='$threadid' AND post.visible=1$attachment_clause");

&

$getpostids=$DB_site->query("
SELECT post.postid FROM post
WHERE post.threadid='$threadid' AND post.visible=1$attachment_clause
ORDER BY dateline $postorder LIMIT ".($limitlower-1).",$perpage
");

How would I make this compatible? :D

[sabret00the]

great hack, noticed it straight away

[Alien]

Any solution on my question above anyone? :)

[David Bott]

Mow installed on AVS Forum (http://www.avsforum.com) and it does help with this large active site.

Anything to make the DB server happier. :)

[weewilly]

I installed this hack and it seemed to work OK.

Until.... you entered a forum and then clicked on a thread to view it. Then, an error was displayed. I had to upload the original file to correct it.

I am using version 2.3.2

Anyone have any idea why this is not working for me? I am an extreme novice and feel silly even posting this.

*********************

Edited to add:

I have it working now. I was editing the file in dreamweaver. When I switched to notepad I was able to modify it and get it working.

[Alien]

Wow, people are really sleeping the past few months...

Help! :D

[Alien]

Ok I've decided to temporarily remove the other hack while I work it out. In the meantime I've installed this one, and the additional query removal from fury and all is working properly so far.. I'll let you know if it breaks anything. :D

Nice work you guys!

[julius]

Installed on vB 2.3.2.

Thanks
:)

[legendarysk8er]

Working fine ob vB 2.3.2.

/me clicks install.

[zsmom]

Works great on 2.3.2. Thanks!!

* zsmom clicks install.

[luke2kz71]

installed on 2.3.4 and working great! :D