Hi guys,
I have an sql query returning a result of nearly 150 000 rows. I use the
PEAR DB::getAll method to retrieve this result as an array which is very big
indeed (about 300Mb). I can't use the MySQL LIMIT statement because I need
to do sorting stuff on the result array.
My concern is that even after processing the last line of the script, the
process continue running for about 20~30 minutes (I am watching running
processes using the unix command 'top'). For information, querying the
database, sorting the array and displaying results with a pager system only
takes 3 minutes.
I have made a short example that reproduces the problem. If you could test
it (IT WILL USE ABOUT 120Mb OF RAM) and tell me if you've got the same
problem it would be nice. You need to create a test database with the
following table :
CREATE TABLE test (
id mediumint(20) NOT NULL,
text varchar(255) NOT NULL default '',
PRIMARY KEY (id)
)
Then populate it with 150 000 rows using the populate.php script.
Finally, execute the query.php script and use top to see if the process
stops running quickly. For me it takes a lot of time to end (more than 20
minutes).
If you empty the database table, remove one character from the string in the
insertInto function and populate it again, it should work correctly.
Thanks for any answer !!
PS : I am using PHP 4.06, MySQL 3.23.41 and Apache on a bi-processor linux
box with 1Gb RAM.
//--------------
// populate.php
//--------------
<?php
require_once("DB.php");
function getmicrotime()
{
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
function insertInto(&$db)
{
for ($cpt = 0; $cpt < 150000; $cpt++)
{
$result = $db->query("INSERT INTO test VALUES('$cpt', 'This is the very
big test n° $cpt. With more text. This time it will take more memory, and
more time to execute. This is very strange. I do not understand why
???????????????')");
}
}
$db = DB::connect("mysql://user:password@localhost/test");
$time_start = getmicrotime();
insertInto($db);
$time_end = getmicrotime();
$time = $time_end - $time_start;
echo "Execution time : $time seconds";
?>
//-------------
//End of populate.php
//-------------
//-------------
//query.php
//-------------
<?php
require_once("DB.php");
function getmicrotime()
{
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
$db = DB::connect("mysql://user:password@localhost/test");
$time_start = getmicrotime();
$result = $db->getAll("SELECT * from test");
$time_end = getmicrotime();
$time = $time_end - $time_start;
echo "Execution time : $time seconds";
?>
//--------------
//End of query.php
//--------------
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
