PDA

View Full Version : improved dot icons - decreases DB load


pwr_sneak
02 Feb 2003, 12:51
This Hack will decrease the Load on your DB-Server.
It adds one small Query to forumdisplay.php and modifies one big, slow query.
If you have lots of posts and $showdots enabled, this is a must.
If you disabled $showdots via Admin-CP because of performance issues, you can savely enable it now :)

Note: this will NOT work with PHP 3.x or lower

UPDATE: I've added instructions to modifiy member2.php, usercp.php and search.php

ScottRoberts
02 Feb 2003, 13:04
Originally posted by pwr_sneak

Note: this will NOT work with PHP 3.x or lower

Or lower? Means it will not work at all...or should this be higher?

pwr_sneak
02 Feb 2003, 13:10
means it will just work with PHP 4 or higher.

Xenon
02 Feb 2003, 13:14
hey, looks very good pwr_sneak.
Joining thread and post table is really a bad thing, so cut them out this way looks good.

Have you some testresults to see the time the two queries take instead of just the one before (just intrested in..)

pwr_sneak
02 Feb 2003, 13:25
Originally posted by Xenon
Have you some testresults to see the time the two queries take instead of just the one before (just intrested in..)
the one before showed up often in my slowqueries.log and took much longer than 10 seconds sometimes.
the two new queries takes 13 millisecondes :p

nuno
02 Feb 2003, 13:45
Are you sure it's faster?
I tested this locally and it doesn't seem to be any faster. :confused:

pwr_sneak
02 Feb 2003, 14:05
if you test it with small post and thread tables it wouldn't make a huge difference, but the original code doesn't scale very well.
my forum got 1.4 million posts and 47k threads and it definitely is MUCH faster

nuno
02 Feb 2003, 14:09
OK, i'll upload the new forumdisplay to live forums and let you know how it goes then.
Back in a mo.

nuno
02 Feb 2003, 14:27
I see some improvement finally, it went from 0.095 to 0.090. :)

Chris M
02 Feb 2003, 15:31
Nice:)

Satan

Dean C
02 Feb 2003, 16:49
Cool hack buddy :)

- miSt

CJi
02 Feb 2003, 19:35
Is there any way this can be implemented for search.php (view new posts) as well? Most of our members use that, which, to say the least, generates a lot of unwanted load.

Thanks :)

MarkB
02 Feb 2003, 19:43
Installed - here's hoping it works ;)

Xenon
02 Feb 2003, 19:59
Originally posted by nuno
I see some improvement finally, it went from 0.095 to 0.090. :)

well for those tests these infos is not very helpfull, if you want to test differences between queries you have to use em in loops.

so compare to run 100 times the big old one with 100 times the small two new and you'll see the effects really :)

pwr_sneak
02 Feb 2003, 20:19
Originally posted by CJi
Is there any way this can be implemented for search.php (view new posts) as well? Most of our members use that, which, to say the least, generates a lot of unwanted load.

Thanks :)
I've updated the instructions for search.php, usercp.php and member2.php :)

djr
02 Feb 2003, 22:29
I noticed some differences in member2.php and usercp.php. You used dojoin instead of dotjoin. I guess it has to be dotjoin like the others, so I changed it in my setup. Can you confirm this as a typo in the textfile/instructions or does it have to be dojoin

- djr

pwr_sneak
03 Feb 2003, 09:20
you're right, this is a typo.
It won't change anything but $dotjoin is right ;)
I found this typo in the original member2.php too (2.2.9).

My Instructions have been updated once again.

CJi
03 Feb 2003, 20:18
pwr_sneak: You rule sir. Thank you :)

cturcich
11 Feb 2003, 01:33
good find, however my forums are still slow with dot folders :(

this has decreased the load time though...

Erwin
11 Feb 2003, 04:57
Interesting... :) Another example of how 2 queries sometimes can be better than 1. :)

Tigga
11 Feb 2003, 06:44
Very nice pwr_sneak! When I tested it my forumdisplay page was loading in 1.2 - 3 seconds. Afterwards it's loading in 0.3 - 0.6. :D

nuno
13 Feb 2003, 23:05
Originally posted by Erwin
Interesting... :) Another example of how 2 queries sometimes can be better than 1. :)
Exactly.
Now go explain that to Allen Ayres. :D

Xenon
14 Feb 2003, 11:32
have installed it on my board, too

i have a small board, but even there the generation time reduced by a half..

Bad Bunny
14 Feb 2003, 13:31
Inteseresting! I think I will save myself the trouble and do it now.
Thank you very much for this performance hack.

agfisdn
06 Apr 2003, 07:33
nice hack!

thanx pwr_sneak !

Alien
03 May 2003, 21:12
Thanks a bunch, works great!

-Jason

Austin Dea
03 May 2003, 21:17
There should be a section in the db for performance enhancing/optimization hacks =).

Nice hack ;)

Xenon
04 May 2003, 21:08
Yesterday at 23:17 Austin Dea said this in Post #27 (http://www.vbulletin.org/forum/showthread.php?postid=390974#post390974)
There should be a section in the db for performance enhancing/optimization hacks =).

Nice hack ;)


i don't think 2 or 3 hacks make such a section really needed ;)

Austin Dea
05 May 2003, 03:43
Yeah, I thought about that after I said it.. plus there can't be that many optimizations =P

Xenon
05 May 2003, 18:43
oh there can ^^

wait until you see vb3 ^^

Austin Dea
05 May 2003, 20:50
Well, but that's all different code =P. I meant there can't be THAT many optimizations to vB2 without re-coding the entire thing ;)

Xenon
06 May 2003, 17:06
ok, that's right.

you have to recode some things to optimize it :)

Gutspiller
05 Jul 2003, 01:12
Instructions say:

in MEMBER2.PHP

a) OLD CODE
--------
if ($showdots and $bbuserinfo[userid] >= 1) {
$dotuserid = "DISTINCT post.userid,";
$dotjoin = "LEFT JOIN post ON (thread.threadid = post.threadid AND post.userid = '$bbuserinfo[userid]' AND post.visible = 1)";
} else {
$dotuserid = "";
$dotjoin = "";
}
NEW CODE
--------
$dotuserid = "";
$dotjoin = "";

What I got:


if ($showdots and $bbuserinfo[userid] >= 1) {
$dotuserid = "DISTINCT post.userid,";
$dotjoin = "LEFT JOIN post ON (thread.threadid = post.threadid AND post.userid = '$bbuserinfo[userid]')";
}

$getthreadids=$DB_site->query("SELECT thread.threadid
FROM thread,subscribethread
WHERE subscribethread.threadid=thread.threadid
AND subscribethread.userid='$bbuserinfo[userid]'
AND thread.visible=1 $datecut $ignoreusers
ORDER BY lastpost DESC
LIMIT ".($limitlower-1).",$perpage
");
$totalthreads=$DB_site->num_rows($getthreadids);

if ($totalthreads>0) {


What do I do?

pwr_sneak
13 Jul 2003, 15:53
you could try to read the code and understand how it works :P
which vbulletin version do you use? if its not the latest stable (2.3.0), try to update it.

sabret00the
10 Aug 2003, 15:37
how many ppl installed this?

edit: check the forumdisplay :)