Mysql Updates

Hi ... I have a question. I have a database of listings, and I have a directory of photos. I have to find out how many photos each of the listings have. So I am doing this...

$sql="SELECT Num from table WHERE numimage=0";
$result=mysql_query($sql);
$numrows=mysql_num_rows($result);
echo "There will be ".$numrows ." Entries";

while ($row=mysql_fetch_array($result)){
$i++;
$path="ls photo/".$row['Num']."_*.jpg |wc -l";
$numimage=`$path`;
echo "NumImage<br>".$numimage ."<br>";

$sqls="UPDATE table SET numimage = $numimage WHERE Num=".$row['Num']."";
$results=mysql_query($sqls);

PHP:

This works fine except the table has 1700 rows, and seems to take about 6 hours, or it just times out and stops. How might I speed this sort of thing up? I suspect it is the queries that are slow, so could I put it into one big query or something?
Thanks.
Red

 

use PHP I/O functions instead of shell commands and pipes, it's way faster. Also Num and numimage should be indexes in the SQL table. Post the structure of the table, there might be other issues as well.

 

As the previous poster remarked, using PHP's directory functions is likely to be a magnitude faster than shell process creation and piping. Try scandir, for starters:
http://www.php.net/manual/en/function.scandir.php

 

I think that the main problem is the sql updates are taking a long time, My table is fairly large and contains many varchar fields, my plan is to try creating a new table with only 3 fields or so, call it num_image or something , then preform the updates on that table, instead of the large table, and join the tables in my queries. I'll let you know how it worked out, thanks for all the help everybody

 

Without seeing the table stucture, it's hard to say, but I bet there are serious improvments that can be made regarding indexing.

 

That could make it work even slower if the indexes are porked