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]

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/sho...hp?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,showemail,homepage,icq,aim,yahoo,joindate,posts,iconpath,biography 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

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 ...

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