PDO Memory Usuage

[berridgeab]

Hello

 

I use PDO to communicate with MySQL. I am trying to get a rowcount of a 600, 000 row table from MySQL using the code below.

 

$pdo = new PDO("mysql:host=127.0.0.1:3306;dbname=$databaseName", $password, $username, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$pdoStatement = $pdo->prepare("SELECT count(*) FROM LargeTable");
$pdoStatement->execute();
echo $pdoStatement->rowCount();

 

When I run the code I get the following error.

 

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 24 bytes)

 

My PHP ini setting for memory limit is 256mb. I don't actually need all 600,000 rows but the only way I can get the total number of rows is by doing a SELECT COUNT. Is there any way of getting this figure with PDO without actually hitting the memory limit.

 

I know the easy answer is to increase the php memory limit, but what happens in 2 years time when my table reaches 20 million rows and by theoretical 1.5gb memory limit doesn't cut it anymore?

[kicken]

If your using the COUNT(*) function in your query, then you just fetch the result like a normal query.

 

example:

$pdo = new PDO("mysql:host=127.0.0.1:3306;dbname=$databaseName", $password, $username, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$pdoStatement = $pdo->prepare("SELECT COUNT(*) as cnt FROM LargeTable");
$pdoStatement->execute();
echo $pdoStatement->fetchColumn();

 

Also, disabling the MYSQL_ATTR_USE_BUFFERED_QUERY option might save you memory, depending on how your processing the query results.  For a COUNT(*) query though it shouldn't make much of a difference as there is only one row in the result set.

 

 

[berridgeab]

Hi yes your right I just read somewhere else.

 

$this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

 

I was setting to true not false, changing to false resolved the memory problem.

 

Thanks again.