Awfully slow mysql script

optimisation tips?

[Will Hamilton]

Afternoon all. I wrote a simple script to grab some information from a database. It goes like this:

- usual connection code -

Order the database by the 'desc' column:
$query="SELECT * FROM images ORDER BY `desc` DESC";

- get result, set num_rows etc. -

$i=0;
while ($i < $num_rows) {

$desc=mysql_result($result,$i,"desc");

if( $desc == $lastdesc ) { }
else {
$albumid=mysql_result($result,$i,"albumid");
$filename=mysql_result($result,$i,"filename");
$title=mysql_result($result,$i,"title");

echo "<div class='imagethumb'> <a href='/fanart/artist.php?a=$desc' class='image'> <img src='/fanart/$albumtitle/image/thumb/$filename'> </a> <center><i>$desc</i></center> </div>";

$lastdesc = $desc;
}

$i++;
}

What this does is first simplyoutput the row's data into a nice image thumb. On that first run through, it sets $lastdesc to be the same as $desc.

Then it moves on to the next row - and using $lastdesc, checks whether it has the same 'desc' as the last row, in which case it won't display as I only want one row to show for each desc.

Then it moves on to the next row and checks again. If the desc is still the same, it'll skip through until it finds a new 'desc' and output that in the image thumb. After that it's off again to repeat.

It works perfectly fine, but it's terribly slow - takes minutes just to get through 200 rows. Any ideas on optimisation? Have I gone about this in totally the wrong way? Thanks.

[Scally_Ally]

could you not use the select distinct function in mysql to make sure that you have no duplicates in the column that you are searching?

$query="SELECT DISTINCT(desc), albumid, filename, title FROM images ORDER BY `desc` DESC";

[Frank_Rizzo]

Is the database the problem or rendering the images? What size are the images?

[LifeinAsia]

It would speed up the display if you added height/width elements to the IMG tag. My guess is that it's not a DB/PHP issue- your browser is just taking a long time to doanload and display all the images. What size are they (file size)?

[Will Hamilton]

They're teeny thumbs; the total image filesize clocks in at about 200kb. It seems to be loading faster now and if the database fetching might not be the issue, then I'll put it down to a slow patch on the server maybe.

The DISTINCT seems just the ticket. I tend to treat mysql more as a logic problem than looking up the appropriate function.. I can't seem to get that code to work however. Is it just (made sure to strip out all the unecessary if/else parts) a case of replacing:

$query="SELECT * FROM images ORDER BY `desc` ASC";

with:
$query="SELECT DISTINCT(desc), albumid, filename, title FROM images ORDER BY `desc` ASC";

?
I get complaints about not being able to select rows if I do that.

I'll add IMG height/width tags too; stops the browser not knowing where the image will be until it arrives.

[FalseDawn]

Using output buffering may also help, if you are not already doing so.
[us3.php.net...]

I am willing to bet that it is a serving and rendering issue rather than a database one - don't forget that each of those images involves a round trip to your server.

[whoisgregg]

From the mysql_result manual page [php.net] (my bold):

When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.

What I would do is first load up your entire DB result into an array using this method:

$query="SELECT * FROM images ORDER BY `desc` DESC";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
 $thumbnails[]=$row;
}
mysql_free_result($result);
// print_r($thumbnails); // uncomment to test output

Then iterate through the $thumbnails array to produce your output. Preferably concatenating it into a variable, then echoing that variable when it's all done (poor man's output buffering, when you don't want to get into managing output buffering for the whole page).

Something like this untested pseudocode:

$count_thumbnails = count($thumbnails); // much faster than doing it in the loop
$thumbnail_output = ''; // just to make sure it's empty
for($i=0; $i<$count_thumbnails; $i++){
$thumbnail_output .= '<li>'.$thumbnail[$i]['title'].'</li>';
}
echo $thumbnail_output;