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
