1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Mysql Updates

Discussion in 'PHP' started by rederick, Aug 28, 2005.

  1. #1
    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...
    SEMrush
    $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
     
    rederick, Aug 28, 2005 IP
    SEMrush
  2. Gmorkster

    Gmorkster Peon

    Messages:
    202
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    Gmorkster, Aug 28, 2005 IP
  3. Willy

    Willy Peon

    Messages:
    281
    Likes Received:
    25
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Willy, Aug 29, 2005 IP
  4. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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 :)
     
    rederick, Aug 29, 2005 IP
  5. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #5
    Without seeing the table stucture, it's hard to say, but I bet there are serious improvments that can be made regarding indexing.
     
    noppid, Aug 30, 2005 IP
  6. Gmorkster

    Gmorkster Peon

    Messages:
    202
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #6
    That could make it work even slower if the indexes are porked
     
    Gmorkster, Sep 3, 2005 IP