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,userid 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,showemail,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/sho...hp?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 ... 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.