[RESOLVED] Optimising MySQL queries

lpa

Due to growing number of users and high server loads caused by MySQL I decided to check all my scripts (written not by me - I am not that a coder) to see if it's possible to reduce the number of MySQL queries and to do some optimisation.

The are a lot of selects from 2 or even 3 tables. See some examples below.
Is it possible (is it necessary) to change (optimise) the queries in these examples?

Thank you.

This is the query to show the online number of friends.

$results = mysql_query("SELECT count(*) as total FROM session, friends WHERE CAST(friends.friendid AS CHAR) = session.user AND friends.ownerid = '$num'")or die(mysql_error());

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

$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);


}

laserlight

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

That is a Bad Thing. You should be using a join instead, e.g.,

$result = mysql_query("SELECT id, uid, text, user, fdate, deleted_reply, deleted,
        photo, fthreads, freplies
    FROM forum_replies, users
    WHERE forum_replies.uid=users.id
        AND forum_replies.threadid=$threadid
    ORDER BY fdate $sortby
    LIMIT $start, $step") or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    // ...
}

lpa

Thank you laserlight.

Unfortunately, I can't use the query as in your example. Many users who posted messages to the forum have deleted their accounts. So, their userids don't exist un usertable anymore and this query will not select all the messages of the thread.

Anyone other suggestions how to reduce the number of queries in this situation?

Thanks.

laserlight

lpa wrote:
Unfortunately, I can't use the query as in your example. Many users who posted messages to the forum have deleted their accounts. So, their userids don't exist un usertable anymore and this query will not select all the messages of the thread.

Use a left join instead.

lpa

This works now.

I am not experienced in creating JOIN queries. Can anyone confirm that everything is OK with this query?

Thanks.

$result=mysql_query("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 LEFT JOIN users ON forum_replies.uid=users.id 
where forum_replies.threadid = '$threadid' 
order by forum_replies.fdate $sortby limit $start,$step") or die(mysql_error());

while($ix=mysql_fetch_array($result)) {

}