Re: PHP4/5 MySQL nested query problem

My "real" application has inserts and does other various forms of processing in the loops which cannot be done from pure sql via join, etc. The test program I attached was just the minimum required to see the performance issue. I agree that PHP is probably a little slower that perl in loops, but not 5 seconds vs. several hours. Kevin On Aug 22, 2004, at 9:49 PM, John Holmes wrote:

Kevin Wormington wrote:

I have ran into an interesting query performance problem that occurs with both 4.3.7 and 5.0.1 against a MySQL 4.1 database. The test program (attached below) works, but performance is extremely sloooow, a perl version of the test program completes in less than 5 seconds with 80k records in table 1 and 138k records in table 2. The PHP program is taking something like 1 to 2 seconds per record. This only seems to occur when you have a query nested inside of another. My test program attached uses mysqli, but mysql functions yield the same result. Any ideas would be appreciated. <? $link = mysqli_connect("localhost","test","test","db"); $qry = "select a,b from table1"; $result = mysqli_query($link,$qry); if (!$result) { echo mysql_error() . "\n"; } while ($data = mysqli_fetch_array($result, MYSQLI_BOTH)) {

    $sql = "select * from table2 where common=".$data[0];
    $result2 = mysqli_query($link,$sql);
    if (!$result2) { echo mysql_error() . "\n"; }
    while ($data2 = mysqli_fetch_array($result2,MYSQLI_BOTH)) {
      $linenum++;
    }
    mysqli_free_result($result2);

} mysqli_free_result($result); mysqli_close($link);

Is there some fundamental reason you're using nested queries instead of a JOIN? PHP is generally slower in loops compared to Perl from the comparisons I've seen, but the real issue is why are you using nested queries to begin with... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php