drisate Posted March 17, 2008 Share Posted March 17, 2008 Hey guys i made a script that shows some sort of board stats with online member list and all.It works fine slows down the page load like hell ... I know the problems come from the querys ... but what ever i try the page keep being slow to load. When i take out all the querrys the page loads instantly. I have 2 sets of querys. The one at the bigining of this script and the one in the loop lower down. <?php $lm_query = mysql_query("Select * FROM jos_users order by id desc limit 0,1") or die (mysql_error()); $lm_result = mysql_fetch_array($lm_query); $lm = $lm_result[username]; $nb_query1 = mysql_query("Select * FROM jos_users") or die (mysql_error()); $number_user = mysql_num_rows($nb_query1); $nb_query2 = mysql_query("Select * FROM jos_sb_messages") or die (mysql_error()); $number_messages = mysql_num_rows($nb_query2); $nb_query3 = mysql_query("Select * FROM jos_sb_messages where parent='0'") or die (mysql_error()); $number_topics = mysql_num_rows($nb_query3); $nb_query4 = mysql_query("Select * FROM jos_sb_messages where parent!='0'") or die (mysql_error()); $number_replies = mysql_num_rows($nb_query4); echo"<br> <TABLE class=std0 height=1 cellSpacing=1 cellPadding=0 width=97% align=center bgColor=#a7a7a9 border=0> <TBODY> <TR> <TD class=title vAlign=top align=middle colSpan=2 height=1 cellpadding='0'><FONT face=Verdana size=1>Stats</FONT></TD></TR> <tr> <TD class=std1 vAlign=top align=left width='50%' bgColor=#f6f6f6 height=1> <TABLE id=AutoNumber3 style='BORDER-COLLAPSE: collapse' borderColor=#111111 cellSpacing=3 cellPadding=3 width='100%' border=0> <TBODY> <TR> <TD width='100%'><font face='Verdana' size='1'>Number of topics</font><FONT face=Verdana><FONT size=1>: <b>$number_topics</b><BR>Number of replies: </FONT></FONT><b><font face='Verdana' size='1'> $number_replies</font></b> <p style='margin-top: 0; margin-bottom: 0'><font face='Verdana' size='1'>Number of posted messages: <b>$number_messages</b></font></p> </TD></TR></TBODY></TABLE></TD> <TD class=std1 vAlign=top align=left width='50%' bgColor=#f6f6f6 height=1> <TABLE id=AutoNumber4 style='BORDER-COLLAPSE: collapse' borderColor=#111111 cellSpacing=3 cellPadding=3 width='100%' border=0> <TBODY> <TR> <TD width='100%'> <p style='margin-top: 0; margin-bottom: 0'><FONT face=Verdana><FONT size=1>Last registred member: </FONT>$lm</font></b></p> <p style='margin-top: 0; margin-bottom: 0'><font face='Verdana' size='1'>Number of members: </font><b><font face='Verdana' size='1'>$number_user</font></b></p> </TD></TR></TBODY></TABLE></TD> </tr> <TR style='FONT-SIZE: 10px; FONT-FAMILY: Verdana, Geneva, Arial, Helvetica, sans-serif'> <TD class=title vAlign=top align=middle colSpan=2 height=1 cellpadding='0'> <p style='margin-top: 0px; margin-bottom: 0px'><FONT face=Verdana size=1>Who's Online</FONT></TD></TR> <TR> <TD class=std1 vAlign=top align=left width='100%' bgColor=#f6f6f6 colSpan=2 height=1> <TABLE id=AutoNumber5 style='BORDER-COLLAPSE: collapse' borderColor=#111111 cellSpacing=3 cellPadding=3 width='100%' border=0> <TBODY> <TR> <TD width='100%'> <P align=center>"; // show online count if ($showmode==0 || $showmode==2) { $query = "SELECT guest, usertype" . "\n FROM #__session" ; $database->setQuery( $query ); $sessions = $database->loadObjectList(); // calculate number of guests and members $user_array = 0; $guest_array = 0; foreach( $sessions as $session ) { // if guest increase guest count by 1 if ( $session->guest == 1 && !$session->usertype ) { $guest_array++; } // if member increase member count by 1 if ( $session->guest == 0 ) { $user_array++; } } // check if any guest or member is on the site if ($guest_array != 0 || $user_array != 0) { echo'We have'; // guest count handling if ($guest_array == 1) { // 1 guest only echo" $guest_array guest "; } else if ($guest_array > 1) { // more than 1 guest echo" $guest_array guests "; } echo $output; // if there are guests and members online if ($guest_array != 0 && $user_array != 0) { echo'and'; } // member count handling if ($user_array == 1) { // 1 member only echo" $user_array member "; } else if ($user_array > 1) { // more than 1 member echo" $user_array members "; } echo'online.'; } } echo' </TD></TR></TBODY></TABLE></TD></TR> <TR> <TD class=std1 vAlign=top align=left width="100%" bgColor=#f6f6f6 colSpan=2 height=1> <TABLE id=AutoNumber6 style="BORDER-COLLAPSE: collapse" borderColor=#111111 cellSpacing=3 cellPadding=3 width="100%" border=0> <TBODY> <TR> <TD width="100%"><p align="center" style="margin-top: 0; margin-bottom: 0">'; // show online member names $query = "SELECT DISTINCT a.username" ."\n FROM #__session AS a" ."\n WHERE a.guest = 0" ; $database->setQuery($query); $rows = $database->loadObjectList(); if ( count( $rows ) ) { // output $i="0"; foreach($rows as $row) { $vip=""; $username=$row->username; $nb_query5 = mysql_query("SELECT * FROM jos_users WHERE username='$username'") or die (mysql_error()); $usertype_result = mysql_fetch_array($nb_query5); $usertype = $usertype_result[usertype]; $nb_query6 = mysql_query("SELECT * FROM vip WHERE site_username='$username'") or die (mysql_error()); $vip_result = mysql_fetch_array($nb_query6); $vip = $vip_result[status]; if ($usertype=="" or $usertype=="Registred"){$usernamea='<i>'.$row->username.'</i>';} if ($vip=="1"){$usernamea='<b><font color="#008000">'.$row->username.'</font></b>';} if ($usertype=="Super Administrator"){$usernamea='<b><font color="#FF9900">'.$row->username.'</font></b>';} if ($usertype=="Manager"){$usernamea='<b><font color="#3399FF">'.$row->username.'</font></b>';} $string = "$usernamea, $string"; $i=$i+1; } } $string = substr($string, 0, -2); if ($string==""){$string = "No members online!";} echo $string; echo "</p></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/ Share on other sites More sharing options...
drisate Posted March 17, 2008 Author Share Posted March 17, 2008 I just found $number_replies = $number_messages - $number_topics; to replace $nb_query4 = mysql_query("Select * FROM jos_sb_messages where parent!='0'") or die (mysql_error()); $number_replies = mysql_num_rows($nb_query4); But thats obviously not enought lol Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493755 Share on other sites More sharing options...
drisate Posted March 17, 2008 Author Share Posted March 17, 2008 I think the most of slow down comes from the loop querys $nb_query5 = mysql_query("SELECT * FROM jos_users WHERE username='$username'") or die (mysql_error()); $usertype_result = mysql_fetch_array($nb_query5); $usertype = $usertype_result[usertype]; $nb_query6 = mysql_query("SELECT * FROM vip WHERE site_username='$username'") or die (mysql_error()); $vip_result = mysql_fetch_array($nb_query6); $vip = $vip_result[status]; Any ideas? it gets very slow when it makes those 2 querys 90 times for all 90members online ... Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493763 Share on other sites More sharing options...
drisate Posted March 17, 2008 Author Share Posted March 17, 2008 No freaks online today? Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493778 Share on other sites More sharing options...
teng84 Posted March 17, 2008 Share Posted March 17, 2008 use primary key then count instead of using numrows... eg.. select count(id) as total rows from table where etc... dont use * or select all fields just select what you need Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493785 Share on other sites More sharing options...
redarrow Posted March 17, 2008 Share Posted March 17, 2008 Look up mysql joins your using to meny select statements....... no man there to meny selects man........... Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493786 Share on other sites More sharing options...
redarrow Posted March 17, 2008 Share Posted March 17, 2008 ALL this is >>>>>>>>>>> $lm_query = mysql_query("Select * FROM jos_users order by id desc limit 0,1") or die (mysql_error()); $lm_result = mysql_fetch_array($lm_query); $lm = $lm_result[username]; $nb_query1 = mysql_query("Select * FROM jos_users") or die (mysql_error()); $number_user = mysql_num_rows($nb_query1); $nb_query2 = mysql_query("Select * FROM jos_sb_messages") or die (mysql_error()); $number_messages = mysql_num_rows($nb_query2); $nb_query3 = mysql_query("Select * FROM jos_sb_messages where parent='0'") or die (mysql_error()); $number_topics = mysql_num_rows($nb_query3); $nb_query4 = mysql_query("Select * FROM jos_sb_messages where parent!='0'") or die (mysql_error()); $number_replies = mysql_num_rows($nb_query4); example only on joins THIS, THE ABOVE IS TO MUCH SORRY! <?php $lm_query = mysql_query("Select jos_users.username, jos_sb_messages FROM job_users as a, jos_sb_messages as b WHERE a.id=b.id") or die (mysql_error()); $lm_result = mysql_fetch_array($lm_query); ?> Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493805 Share on other sites More sharing options...
drisate Posted March 17, 2008 Author Share Posted March 17, 2008 Thats what i did at first teng84 but then i started trying alternatives and thats how i anded up with the code i posted. Thanks redarrow i am gona try that and see what happens :-) But i think i will take a good night sleep first. Thx for the help guys! Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493806 Share on other sites More sharing options...
redarrow Posted March 17, 2008 Share Posted March 17, 2008 ok m8 you rest i will aswell, you really need joins ok m8 mine might be wrong but try good night.. Quote Link to comment https://forums.phpfreaks.com/topic/96478-script-optimysation-help/#findComment-493814 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.