Script Optimysation Help

drisate · 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>";

?>

drisate · 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

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

drisate · March 17, 2008

No freaks online today?

teng84 · 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

redarrow · March 17, 2008

Look up mysql joins your using to meny select statements.......

no man there to meny selects man...........

redarrow · 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);
?>

drisate · 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!

redarrow · March 17, 2008

ok m8 you rest i will aswell, you really need joins ok m8 mine might be wrong but try good night..

Sign In

Script Optimysation Help

Recommended Posts

drisate

Link to comment

Share on other sites

drisate

Link to comment

Share on other sites

drisate

Link to comment

Share on other sites

drisate

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

drisate

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information