Big-time showthread.php Optimiziation

There had to be a way to speed this section of code
up, since it was the slowest loading page on my board.
After looking at how it was handled, I discovered that the
script executes a new mySQL query for every post displayed
on that page. This query is used to suck out relevant user-
specific information not stored in the post table (such as
username, post count, icq, etc.

So on a page with 30 posts, you'd have 30 resource-taking
SQL calls. Thanks to old-school UBB'er TDawg for helping me
realize that a join was the answer! :)

Any forum user should make this change, but especially
those who notice a bit of latency when viewing an
individual post. (the main factor of speed in this is the
size of your user database -- not the number of
posts/threads). So, here are the changes, which include one
hella-big SELECT statement... ;)

Perhaps John will integrate this into the next version? Its
a real resource-saver, and doesn't have any negative side-
effects (yet :))

Note: Please backup showthread.php before
performing this modification!

Look for:
$posts=$DB_site->query("SELECT dateline,postid,pagetext,allowsmilie,signature AS showsignature,title,ipaddress,iconid,username,user id FROM post WHERE threadid=$threadid AND visible=1 ORDER BY dateline $postorder LIMIT $limitlower,$perpage");
Replace with:
$posts=$DB_site->query("SELECT post.dateline as dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as allowsmilie,post.signature AS showsignature,post.title as title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as fakename,post.userid as userid, user.userid as userid,user.email as email,user.username as username,user.usertitle as usertitle,user.signature as signature,user.showemail as showemail,user.homepage as homepage,user.icq as icq,user.aim as aim,user.yahoo as yahoo,user.joindate as joindate,user.posts as posts FROM post,user WHERE post.threadid=$threadid AND visible=1 AND user.userid = post.userid ORDER BY dateline $postorder LIMIT $limitlower,$perpage");
Look for:
$userinfo=$DB_site->query_first("SELECT userid,email,username,usertitle,signature,showemai l,homepage,icq,aim,yahoo,joindate,posts FROM user WHERE userid=$userid");
Replace with:
$userinfo = $post;
Look for:
$username=htmlspecialchars($post[username]);
Replace with:
$username=htmlspecialchars($post[fakename]);

That's all -- let me know if you notice any speed improvements! :-)

[edit: adjusted for width]

[Edited by Stallion on 08-28-2000 at 10:49 PM]

Stallion,
Seems faster to me. Of course, its kind of late at night so the load on the server is kind of low as well.
One thing to note, this part

user.avimage as avimage
is not in my version. is it a hack?
Nice work. Gotta love optimizations. Now can you do anything for the member.php?action=showprofile?

Make note that if you paste that in, you will break any hacks that deal with showthread (avatar,location,stars,etc) so be ready for that. Nothing ill will come of it, your hack will just dissapear from the posts.

It appears "avimage" was part of the avatar hack, so I removed that section for my inital post. If you do have any hacks which modified this SELECT statement, you'll want to go through and re-hack any modified steps.

If you don't feel comfortable doing that, and think the loading speed for showthread.php is fine, then don't worry about making these changes at all.

"avimage" is probably part of Moonwolf's avatar hack.

It seems that in your rush for glory you forgot one small detail. You are doing a join on the post table and user table where user.userid = post.userid

Only problem is that if I allow Guests to post - as I do on one forum - they have a userid of 0 which has no match in the user table, hence the message is now empty.

Case in point :

http://www.thebangles.net/forums/showthread.php?threadid=13

I cheated and created a "Guest" user with userid of 0 so there would be something to match. You would need to come up with something cleaner though.

The threads do seem faster now though :) This little "hack" should help out forums like Martins, wrestlingtalks, etc immensely as just imagine 120 users, each issueing 30 (say you allow 30 posts to show on a thread page) mysql select statements at once (3600 statements!). That is now cut down to 120 select statements with slight overhead of making a join. I only have 230 users (6000+) posts and it makes a big difference as I don't subtly see the thread "build" itself now, it just pops on the screen at once.

[Edited by rangersfan on 08-29-2000 at 12:24 AM]

looking good :D ... how do you keep track of other hacks you add into a php file like showthread.php ?

i have the following hacks installed

locations/interest hack
rangersfan's avatar hack
search result new post/last post jump arrows

so doing the above changes to showthread.php, to re add the hacks do i just follow the original hack's instructions ?

I had installed the PM hack, and it had some changes to the second MySQL query above. However, it was one word off (and it looked like it was tacked on) so I just replaced it. I tacked on "user.receivepm as receivepm" to the first query statement above, and it's working fine now.

LMK if I've done something terribly wrong, as I'm a relative newbie to PHP and MySQL...

I implemented the hack (including the addition of the the "user.receivepm as receivepm" which Armage Bedar suggested) and it seems to be just great so far. One question though: are there any changes I ought to add if I have the Poll hack?

Thanks,

Bira

I don't have the private messaging/poll hacks installed, so I can't give specific info. All I can suggest is that if the hack modified the SQL statement listed, you go back and repeat whatever steps modified that statement, but base it off the new SQL statement I posted.

rangersfan: I don't have guest posting enabled on my forum, so I wasn't aware of that problem. I'll look more into the way vB handles non-registered posts and see if I can come up with an update.

Originally posted by Stallion
rangersfan: I don't have guest posting enabled on my forum,
so I wasn't aware of that problem. I'll look more into the
way vB handles non-registered posts and see if I can come
up with an update.

Actually this is more of a SQL syntax thing, you need to do
a left outer join between the post and user table, this way
all posts (within the criteria of course) will be returned
even when there is no user record available (as for guests).


Something like:

SELECT <fields> FROM post LEFT JOIN user ON (user.userid = post.userid) WHERE post.threadid=$threadid AND visible=1 ORDER BY dateline $postorder LIMIT $limitlower,$perpage


This is untested, but something along this line should work.

-Chris

[edit for readability]

[Edited by Chris Schreiber on 08-29-2000 at 10:32 AM]

Chris I changed it to what you posted and it seems to work

http://www.thebangles.net/forums/showthread.php?threadid=13

Wow! I am glad it worked... I know that dummy "0" user works too, I was just concerned it might mess something else up (having records with 'magic' key values like that sometimes cause problems).

-Chris

Yes it messed things up by forcing the key to 0 :)

WOW.

I made the mods, then the corollary mods. An incredible improvement.

OK. I'm a bit confused. I made the original mods by Stallion (less the avatar piece) and it seemed to work fine. Does the subsequent piece by Chris work correctly? That '0' dummy piece left me wondering...

Yes I've been told it works just fine, and fixes the problem
if you allow your guests to post anonymously.

From the orignial where it says
Replace with:

$posts=$DB_site->query("SELECT post.dateline as dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as allowsmilie,post.signature AS showsignature,post.title as title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as fakename,post.userid as userid, user.userid as userid,user.email as email,user.username as username,user.usertitle as usertitle,user.signature as signature,user.showemail as showemail,user.homepage as homepage,user.icq as icq,user.aim as aim,user.yahoo as yahoo,user.joindate as joindate,user.posts as posts FROM post,user WHERE post.threadid=$threadid AND visible=1 AND user.userid = post.userid ORDER BY dateline $postorder LIMIT $limitlower,$perpage");

Change that to:

$posts=$DB_site->query("SELECT post.dateline as dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as allowsmilie,post.signature AS showsignature,post.title as title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as fakename,post.userid as userid, user.userid as userid,user.email as email,user.username as username,user.usertitle as usertitle,user.signature as signature,user.showemail as showemail,user.homepage as homepage,user.icq as icq,user.aim as aim,user.yahoo as yahoo,user.joindate as joindate,user.posts as posts FROM post LEFT JOIN user ON (user.userid = post.userid) WHERE post.threadid=$threadid AND visible=1 ORDER BY dateline $postorder LIMIT $limitlower,$perpage");


And you should be good to go :)

-Chris


[Edited by Chris Schreiber on 08-30-2000 at 02:53 PM]

where would you add the to the above optimised code, rangersfan's avatar for iconpath ? and ed's location, interest hack for the biography field ?

in my existing showthread.php i have

$userinfo=$DB_site->query_first("SELECT userid,email,username,usertitle,signature,showemai l,homepage,icq,aim,yahoo,joindate,posts,iconpath,b iography FROM user WHERE userid=$userid"); with the added iconpath and biography

what do i change it to or add ? do i just add iconpath, biography to the end of it or something like user.iconpath as iconpath, user.biography as biography ?

Originally posted by eva2000
.... something like user.iconpath as iconpath, user.biography as biography

Adding that to the list of fields should do it.

-Chris

Originally posted by Chris Schreiber
Originally posted by eva2000
.... something like user.iconpath as iconpath, user.biography as biography Adding that to the list of fields should do it. -Chris

yep it worked my avatar, location/interest addons are still with me ...so far so good... but being on cable i can't really tell :D ...

[Edited by eva2000 on 08-30-2000 at 08:45 PM]

Given the wicked speed increase we've seen with this modification, I think Jelsoft should post a modified showthread.php in the member's section. Seriously.

yes they should... anyway i took martins advice and created a hacks folder on my pc to store each hack php file along with a backup of the previous original :D

After implementing this on the showthread pages all posts appear as year 1969 including peoples registration date.

Any idears?

nm :) fixed

No need to post the same question in multiple places. All problems with the avatar hack go in that thread only. I have posted a solution to your problem already. Refer back to Avatar thread.

yeah i wasn't sure if it was part of the optimization hack i did after i installed the avatar hack

I tried adding the receive pm segment in and get the following error. Did I add it in the wrong place?

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

Database error in vBulletin: Invalid SQL: SELECT post.dateline as
dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as
allowsmilie,post.signature AS showsignature,post.title as
title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as
fakename,post.userid as userid, user.userid as userid,user.email as
email,user.username as username,user.usertitle as usertitle,user.signature as
signature,user.showemail as showemail,user.homepage as homepage,user.icq as
icq,user.aim as aim,user.yahoo as yahoo,user.joindate as
joindate,user.posts as posts FROM post,user.receivepm as receivepm,user WHERE
post.threadid=24332 AND visible=1 AND user.userid = post.userid ORDER BY
dateline LIMIT 0,15
mysql error: Table 'user.receivepm' doesn't exist
mysql error number: 1146
Date: Friday 15th of September 2000 06:34:18 PM
Script: /forum/showthread.php?threadid=24332
Referer: http://www.dsschat.com/forum/forumdisplay.php?forumid=2

Originally posted by risestar
I tried adding the receive pm segment in and get the following error. Did I add it in the wrong place?

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

Database error in vBulletin: Invalid SQL: SELECT post.dateline as
dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as
allowsmilie,post.signature AS showsignature,post.title as
title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as
fakename,post.userid as userid, user.userid as userid,user.email as
email,user.username as username,user.usertitle as usertitle,user.signature as
signature,user.showemail as showemail,user.homepage as homepage,user.icq as
icq,user.aim as aim,user.yahoo as yahoo,user.joindate as
joindate,user.posts as posts FROM post,user.receivepm as receivepm,user WHERE
post.threadid=24332 AND visible=1 AND user.userid = post.userid ORDER BY
dateline LIMIT 0,15
mysql error: Table 'user.receivepm' doesn't exist
mysql error number: 1146
Date: Friday 15th of September 2000 06:34:18 PM
Script: /forum/showthread.php?threadid=24332
Referer: http://www.dsschat.com/forum/forumdisplay.php?forumid=2



Yes the text "user.receivepm as receivepm" must be inserted at the end of the SELECT fields list but before the FROM tables - i.e. SELECT .....list of fields..... , user.receivepm as receivepm FROM post, user ...etc etc...

Watch for the comma that separates each field.

Hope that helps

This hack is really cool and did speed up the whole vB forum. :)

This PM thing is confusing now :).I'm using PM and would love to try this speed up thingy.Now can someone write down what needs to be changed so that the PM system still works?

Yes could someone please post the full hack to be used with pm?
Thanks in advance!

Thanks hutchcft!

conan, the first replacement line with pm



$posts=$DB_site->query("SELECT post.dateline as dateline,post.postid as postid,post.pagetext as pagetext,post.allowsmilie as allowsmilie,post.signature AS showsignature,post.title as title,post.ipaddress as ipaddress,post.iconid as iconid,post.username as fakename,post.userid as userid, user.userid as userid,user.email as email,user.username as username,user.usertitle as usertitle,user.signature as signature,user.showemail as showemail,user.homepage as homepage,user.icq as icq,user.aim as aim,user.yahoo as yahoo,user.joindate as joindate,user.posts as posts,user.receivepm as receivepm FROM post,user WHERE post.threadid=$threadid AND visible=1 AND user.userid = post.userid ORDER BY dateline $postorder LIMIT $limitlower,$perpage");

it seems to work great for me!

thanx for this one Stallion. :)

Does anyone know if this is fixed with 1.14?
thx.

Originally posted by dwh
Does anyone know if this is fixed with 1.14?
thx.

If you install 1.1.4 you do not need this hack...

If you still want to use PM install the 1.2.5 version.