Optimising query

Forum Moderators: open

Message Too Old, No Replies

Optimising query

Reducing the number of queries per page

gcan

9:39 am on Apr 29, 2009 (gmt 0)

Hello,

Next lines are from my forum script. The thread consists of 20 messages per page, so there are 20 queries to receive user information for each message.

Any ideas how to get user information without sending 20 queries per page to the usertable?

Thanks.

$tmp=mysql_query("SELECT id, uid, text, user, fdate, deleted_reply, deleted FROM forum_replies where threadid='$threadid' order by fdate $sortby limit $start,$step") or die(mysql_error());
while($ix=mysql_fetch_array($tmp)) {

$userinfo=mysql_query("SELECT photo, fthreads, freplies FROM users where id='".$ix['uid']."'") or die(mysql_error());
list($photo, $fthreads, $freplies) = mysql_fetch_row($userinfo);}

coopster

1:13 pm on Apr 29, 2009 (gmt 0)

Execute one query. Assuming there is a single entry per user in your users table -- have you considered a JOIN on the uid?

rocknbil

3:33 pm on Apr 29, 2009 (gmt 0)

^ ^ ^ Something like

$select = "select forum_replies.id,
forum_replies.uid,
forum_replies.text,
forum_replies.user,
forum_replies.fdate,
forum_replies.deleted_reply,
forum_replies.deleted,
users.photo,
users.fthreads,
users.freplies
from forum_replies, users where forum_replies.uid=users.id
and forum_replies.threadid='$threadid'
order by forum_replies.fdate
$sortby limit $start,$step";

$userinfo=mysql_query("$select") or die(mysql_error());
while(list($id,$uid,$text,$user,$fdate,$deleted_reply,$deleted,$photo,$fthreads,$freply)=mysql_fetch_array($userinfo)){
// output row
}

Be sure to compile $sortby with full table name:

$sortby="forum_replies.fdate";