Too many Database Calls

Question

I have an auto refresh script that refreshes a page every 3 seconds. The page it refreshes ha few database queries. When I get 20 or more members on my site the server crashes because of all the calls to the database. Is there another way to check for changes in the database?

//Can't Chat
$strFind="SELECT * FROM cantchat";
$result=mysql_query($strFind) or die(mysql_error());
while($row=mysql_fetch_array($result)) 
{
$id=$row['memid'];

$strsql="DELETE FROM cometchat_chatroommessages WHERE userid=\"$id\"";
$chkrow1=mysql_query($strsql,$connect) or die(mysql_error());

}


if($banned<1){
$strFind="SELECT * FROM ttourmember WHERE memberid=\"$curmemid\"";
$result=mysql_query($strFind) or die(mysql_error());
$row=mysql_fetch_array($result);
$membername=$row['membername'];
$console=$row['system'];

# timeout - how long should it take before visitors are no longer 'online'? (in     minutes)
define ('timeout', 20);
// check if visitor is already in the table
$lastactive =time();
$strFind="SELECT COUNT(*) AS total FROM online WHERE `memberid`=\"$curmemid\"";
$result=mysql_query($strFind) or die(mysql_error());
$row=mysql_fetch_array($result);
$imonline=$row['total'];

if ($imonline<1) {
// Insert new visitor
$strsql="INSERT INTO    online(lastactive,memberid,membername,console,ipaddress,accountactive)   VALUES(\"$lastactive\",\"$curmemid\",\"$membername\",\"$console\",\"$ip\",'Y')";
$chkrow1=mysql_query($strsql,$connect) or die(mysql_error());
$onlineid=mysql_insert_id();//copied        
} else {
// Update exisiting visitor
$strsql="UPDATE online SET     `lastactive`=\"$lastactive\",membername=\"$membername\",console=\"$console\",ipaddress=\"$i  p\" WHERE `memberid`=\"$curmemid\"";
mysql_query($strsql,$connect) or die(mysql_error());
$chkrow5=mysql_affected_rows($connect);

$strsql="UPDATE ttourmember SET `ipadd`=\"$ip\" WHERE `memberid`=\"$curmemid\"";
mysql_query($strsql,$connect) or die(mysql_error());
$chkrow5=mysql_affected_rows($connect);

}

// Remove any inactive visitors
$inactive = time()-21;

//Who's Online list
$strFind="SELECT * FROM online WHERE lastactive< $inactive AND `stay`<>'Y'";
$result=mysql_query($strFind) or die(mysql_error());
while($row=mysql_fetch_array($result))
{
$friendid=$row['memberid'].",";

$strsql="DELETE FROM friends WHERE toid=\"$friendid\" OR fromid=\"$friendid\"";
$chkrow1=mysql_query($strsql,$connect) or die(mysql_error());

}

$strsql="DELETE FROM online WHERE lastactive < $inactive AND stay<>'Y'";
mysql_query($strsql,$connect) or die(mysql_error());
$chkrow5=mysql_affected_rows($connect);

//Can Chat
$query_chat = "SELECT * FROM online WHERE `memberid`<>\"$curmemid\" ORDER BY membername     DESC";
$chat = mysql_query($query_chat) or die(mysql_error());
$row_chat = mysql_fetch_assoc($chat);
$totalRows_chat = mysql_num_rows($chat);


//Count Pending Games
$strFind="SELECT COUNT(*) AS total FROM tgamertournament WHERE `memberid` =     \"$curmemid\" AND `pending`='Y'";
$result=mysql_query($strFind) or die(mysql_error());
$row=mysql_fetch_array($result);
$tpgames=$row['total'];

$strFind="SELECT COUNT(*) AS total FROM tgamertournament WHERE `targetto` =   \"$curmemid\" AND `pending`='Y'";
$result=mysql_query($strFind) or die(mysql_error());
$row=mysql_fetch_array($result);
$topgames=$row['total'];

$pgames=$tpgames+$topgames;

//Steam Received Sent Challenge 
$query_stgames = "SELECT * FROM tgtournamentrequest WHERE `gamer`=\"$curmemid\" AND  `active`<>'D' AND `scored`<>'Y' AND `type`='S' AND `startfteam`='Y' AND `isaccepted`='N'    AND `startsteam`='N' ORDER BY tournamentid DESC LIMIT 3";
$stgames = mysql_query($query_stgames) or die(mysql_error());
$row_stgames = mysql_fetch_assoc($stgames);
$totalRows_stgames = mysql_num_rows($stgames);

//Waiting For Fteam Sent Challenge To Get Accepted
$query_wftgames = "SELECT * FROM tgtournamentrequest WHERE `fteam`=\"$curmemid\" AND `active`<>'D' AND `scored`<>'Y' AND `type`='S' AND `startfteam`='Y' AND `isaccepted`='N' AND `startsteam`='N' ORDER BY tournamentid DESC LIMIT 3";
$wftgames = mysql_query($query_wftgames) or die(mysql_error());
$row_wftgames = mysql_fetch_assoc($wftgames);
$totalRows_wftgames = mysql_num_rows($wftgames);

//List Posted Pending Games Accepted
$query_apostedgames = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\"        OR `fteam`=\"$curmemid\" AND `active`<>'D' AND `scored`<>'Y' AND `type`='P' ORDER BY     tournamentid DESC LIMIT 3";
$apostedgames = mysql_query($query_apostedgames) or die(mysql_error());
$row_apostedgames = mysql_fetch_assoc($apostedgames);
$totalRows_apostedgames = mysql_num_rows($apostedgames);

 //List Posted Pending Games
$query_postedgames = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\"   OR `fteam`=\"$curmemid\" AND `active`<>'D' AND `scored`<>'Y' AND `type`='P' ORDER BY   tournamentid DESC LIMIT 3";
$postedgames = mysql_query($query_postedgames) or die(mysql_error());
$row_postedgames = mysql_fetch_assoc($postedgames);
$totalRows_postedgames = mysql_num_rows($postedgames);

//Report Score
$query_score = "SELECT * FROM tgtournamentrequest WHERE `fteam` = \"$curmemid\" AND    `gamer`<>'0' AND `isaccepted`='Y' AND `active`='Y' AND `startfteam`='Y' AND  `startsteam`='Y' AND `scored` <>'Y' ORDER BY tournamentid DESC LIMIT 3";
$score = mysql_query($query_score) or die(mysql_error());
$row_score = mysql_fetch_assoc($score);
$totalRows_score = mysql_num_rows($score);

//Steam Pending Score
$query_pscore = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\" AND   `fteam`<>'0' AND `active`='Y' AND `startfteam`='Y' AND `startsteam`='Y' AND scored <>'Y' ORDER BY tournamentid DESC LIMIT 3";
$pscore = mysql_query($query_pscore) or die(mysql_error());
$row_pscore = mysql_fetch_assoc($pscore);
$totalRows_pscore = mysql_num_rows($pscore);

//Delete Games
$query_dmoline = "SELECT * FROM tgamertournament WHERE `memberid` = \"$curmemid\" AND `deleted`<>'Y' AND `scored`<>'Y' AND `accepted` <>'Y' AND `targetto`='0' AND `isactive`='Y'";
$dmoline = mysql_query($query_dmoline) or die(mysql_error());
$row_dmoline = mysql_fetch_assoc($dmoline);
$totalRows_dmoline = mysql_num_rows($dmoline);

Answer 1

Is there another way to check for changes in the database?

Try show processlist in phpMyAdmin or a mysql-shell while your site is "under attack"... you should see the running queries and how long they are running. Maybe you find some heavy queries.

Answer 2

Well, first of all I wouldn't do the following:

$strFind="SELECT * FROM online WHERE lastactive< $inactive AND `stay`<>'Y'";
$result=mysql_query($strFind) or die(mysql_error());
while($row=mysql_fetch_array($result))
{
$friendid=$row['memberid'].",";

$strsql="DELETE FROM friends WHERE toid=\"$friendid\" OR fromid=\"$friendid\"";
//echo $strsql;
//exit;
$chkrow1=mysql_query($strsql,$connect) or die(mysql_error());

}

Instead grab all the id's and delete them at once. I'd also only fetch the one column I need to do this:

$strFind="SELECT memberid FROM online WHERE lastactive< $inactive AND `stay`<>'Y'";
$result=mysql_query($strFind) or die(mysql_error());
$offline = array();
while($row=mysql_fetch_array($result))
{
  $offline[]="'{$row['memberid']}'";
}
$friendids = implode(',', $offline);

$strsql="DELETE FROM friends WHERE toid IN ($friendids) OR fromid IN ($friendids)";
$chkrow1=mysql_query($strsql,$connect) or die(mysql_error());