Using Unbuffered Queries in PHP to Reduce Memory Usage

When developing a PHP application that needs to scan a table with tens of thousands of rows, the default buffered query mode can quickly exhaust the allowed memory limit, leading to a fatal error such as "Allowed memory size of 268 435 456 bytes exhausted".

Buffered queries retrieve the entire result set from the database server and store it in PHP memory before the script can start processing, which is efficient for small datasets but consumes a lot of RAM for large ones.

Unbuffered queries, on the other hand, fetch rows one by one from the server, keeping memory usage low at the cost of keeping the database connection busy until all rows are read.

Below are three ways to perform unbuffered queries in PHP.

Method 1: mysqli

<?php
$mysqli  = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

if ($uresult) {
   while ($row = $uresult->fetch_assoc()) {
       echo $row['Name'] . PHP_EOL;
   }
}
$uresult->close();
?>

Method 2: PDO

<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
   while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
       echo $row['Name'] . PHP_EOL;
   }
}
?>

Method 3: mysql (deprecated)

<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db   = mysql_select_db("world");
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
   while ($row = mysql_fetch_assoc($uresult)) {
       echo $row['Name'] . PHP_EOL;
   }
}
?>

Use buffered queries for small result sets where speed is critical, and switch to unbuffered queries for large datasets to keep memory consumption low.