CODEWITHSUNDEEP
phpmysql
kingjtiv
kingjtiv

Reputation: 153

PHP Query Loading too slow. How to speed up?

Does anyone know a more efficient way to write the following. my load time over a minute right now. This what I came up with after an even longer codeset.

$max = $i + 25;
while ($i < $max)
  {
    $memberno = $memberid;
    if($pulledid!=$memberid)
      {
        $pulledid = mysql_result($result99,$i,"id_member");
        $sql_statement = "Select vinyl_fortrade.ImageID,vinyl_fortrade.MemberID from vinyl_fortrade where exists (Select ImageID from vinyl_wanted WHERE MemberID = $memberno AND vinyl_wanted.ImageID = vinyl_fortrade.ImageID) AND vinyl_fortrade.MemberID=$pulledid";
        $result=mysql_query($sql_statement)or die(mysql_error());    
        $num=mysql_numrows($result);
        if($num>0)
          {
            $sql_statement2 = "Select vinyl_wanted.ImageID, vinyl_wanted.MemberID from vinyl_wanted where exists (Select ImageID from vinyl_fortrade WHERE MemberID = $memberno AND vinyl_wanted.ImageID = vinyl_fortrade.ImageID) AND vinyl_wanted.MemberID=$pulledid";
            $result2=mysql_query($sql_statement2)or die(mysql_error());                
            $num2=mysql_numrows($result2);
            $membername = mysql_result($result99,$i,"smf_members.member_name");
            $userid2 = mysql_result($result99,$i,"smf_members.id_member");
            if ($num2 > 0)
              {
                $lastlogged = mysql_result($result99,$i,"smf_members.last_login");
                echo '<td><a href="http://vinylmationconnection.com/forum/index.php?action=pm;sa=send;    u='.$userid.'"style="color: #FFF">'.$membername.'</a><br>Last Logged In:<br>';
                echo date('Y-m-d', $lastlogged);
                echo ':</td>';
                echo '<td>';
                $n = 0;
                $m = 0;
                while ($n < $num)
                  {
                    $title=mysql_result($result,$n,"vinyl_fortrade.ImageID");
                    $sql_statementj = "Select * from vinyl_figures WHERE ImageID = '$title'";
                    $resultj=mysql_query($sql_statementj)or die(mysql_error());  
                    $seriess=mysql_result($resultj,0,"vinyl_figures.Series");
                    echo '<img  src="http://vinylmationconnection.com/images/p'.$seriess.'/'.$title.'.jpg">';
                    $n++;
                    if($n%4=="0")
                      echo "<br>";
                  }
                echo '</td><td>';
                while ($m < $num2)
                  {
                    $title=mysql_result($result2,$m,"vinyl_wanted.ImageID");
                    $sql_statementj = "Select Series from vinyl_figures WHERE ImageID = '$title'";
                    $resultj=mysql_query($sql_statementj)or die(mysql_error());  
                    $seriess=mysql_result($resultj,0,"vinyl_figures.Series");
                    echo '<img src="http://vinylmationconnection.com/images/p'.$seriess.'/'.$title.'.jpg">';
                    $m++;
                    if($m%4=="0")
                      echo "<br>";
                  }                              
                echo '</td> </tr>';
              }
          }
      }
    $i++;
  }

Please let me know if ths code is self explanatory.

Upvotes: 0

Views: 1104

Answers (4)

DaGhostman Dimitrov
DaGhostman Dimitrov

Reputation: 1626

Except for the answeres that are alredy mentined I could suggest you to use mod_deflate to compress the page before sending it to the browser and in should have some effect. Althrough if you are on local environment that also may cause a slow execution, becouse of the load on the machine.

And as well I think that you could lose the first while by using LIMIT $i, 25 in the query I think that it will have the same effect. To when you fetch them all with a simple foreach and it is done and I would suggest you to push as much as possible in a single query that will increse the load a bit but it should decrease the loading time.

I am not a 100% professional but am trying to be of help.

Upvotes: 0

Ray Paseur
Ray Paseur

Reputation: 2194

The WHERE EXISTS clause is almost certainly the culprit, and it should be refactored. General guidelines for slow queries...

  1. Use EXPLAIN SELECT to see what the query is really doing
  2. Add indexes on all columns used in WHERE, JOIN, ORDER, GROUP
  3. Avoid SELECT * and instead SELECT only the columns you need
  4. Add LIMIT to all queries that do not require a complete table scan.

Upvotes: 0

Theolodis
Theolodis

Reputation: 5102

$sql_statementj = "Select * from vinyl_figures WHERE ImageID = '$title'";
$sql_statementj = "Select Series from vinyl_figures WHERE ImageID = '$title'";

in these 2 statements you seem to make a mysql request for the same file if I got your code right. make these requests outside the loops, this should do some work.

Upvotes: 2

Peter Kiss
Peter Kiss

Reputation: 9319

Write a stored procedure (maybe a simple query/View could be enough) to do all these query stuff and watch for database indexing.

Upvotes: 0

Related Questions