loop causing high server load any help appreciated

[blogit]

I have coppermine gallery running and to display images on my index page i have a script that pulls random images and puts them in a table. I just got an email from my server admins that it's causing really high cpu usage, can someone tell me which loop might be causing the problem and suggest a fix? Maybe combine the loops to close the table all in one loop? Thank you

 

<table cellspacing="0" cellpadding="0">
<?php
$query = "SELECT filename, filepath, pid FROM cpg14x_pictures WHERE pwidth > pheight ORDER BY RAND() LIMIT 8";

$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); 
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4;
    while($row = mysql_fetch_array($result))        
   {
       // make the variables easy to deal with
       extract($row);

       // open row if counter is zero
       if($i == 0)
          echo "<tr>";

       // make sure we have a valid product
       if($pid != "" && $pid != null)
          print '<td>'.'<div class="img-shadow">'.'<a href="/Gallery/displayimage.php?pos=-'.$pid.'">'.'<img src="/Gallery/albums/' . $filepath . 'thumb_' . $filename . '">'.'</a>'.'</div>'.'</td>';
//print '<td>'.'<img src="/Gallery/albums/'.$filepath.'thumb_'.$filename.'id="thephoto_'.$pid.'onLoad="initImage("thephoto_"'.$pid.'alt="Ikaria Gallery"'.'">'. '</td>';

       // increment counter - if counter = max columns, reset counter and close row
       if(++$i == $max_columns) 
       {
           echo "</tr>";
           $i=0;
       }  // end if 
   } // end while
} // end if results

// clean up table - makes your code valid!
if($i < $max_columns)
{
    for($j=$i; $j<$max_columns;$j++)
        echo "<td> </td>";
}
?>
</tr>
</table>

[lemmin]

How many pictures do you have in the database?

 

When you use ORDER BY RAND(), it actually creates a random number for EVERY row in the table and then chooses the 8. If you have a lot of pictures, it would be very processor intensive. To cut down on that, use PHP to make 8 random numbers and make the criteria check for the imageID (assuming you have a uniqeu id number for each row) using a set of the numbers you created.

[tibberous]

I'd tell them to piss off - or switch to switch to a VPS. My site runs on a VPS and it works fine.

[blogit]

Theres a couple of hundred photos with more being added all the time lemmin can you give me an example of how to have php get 8 random images, is it the loops causing the high cpu usage or the call to get random photos?

[BK87]

looks fine to me... you might want to cache your "data" so db doesn't work 24/7 if a person sees the same thing every time why pull it out of db... especially if its a lot of people at the time

[cunoodle2]

Here are 2 different options for you...

 

1.

Get the number of rows in the database.

Then use php rand function to come up with 8 different values.

Use an SQL query to select the 8 values that came from the php rand function

 

2.

Create a cron job that generates a series of like 25 different sets of pictures (this could like run at midnight).  It would be basically indexing your site for faster future visits.  Then each time a user visits the site it will pick one of the 25 pre selected groups of pictures.  Since the cron runs each night then next day there would be a whole new set of 25 different image groups.

[lemmin]

Since you don't have any loops that are really that bad, I would say it is very likely that the cpu load is resulting from the MYSQL RAND function. To do it in PHP, do something like this:

$qry = mysql_query("SELECT COUNT(ImageID) as cnt FROM Images");
$row = mysql_fetch_assoc($qry);
$randoms = array();
for ($i=0; $i<8; $i++)
   $randoms[] = rand(1, $row['cnt']);

$qry = mysql_query("SELECT * FROM Images WHERE ImageID IN ($randoms)");
//...

 

I think I did that right. It is assuming that your uniqeu key (ImageID in this example) starts at 1, otherwise, you can change 1 to whatever it starts at.