Download heavy CSV - PHP MySQL Query Download to CSV File package blog

Queries are using the buffered mode by default.

This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process.

<?php
/**
 * PHP code
 */

$memoryUsagePrevious = memory_get_usage();
$sth = $db->query("SELECT * FROM table");
$memoryUsageLater = memory_get_usage();
$memoryUsageByQuery = $memoryUsageLater - $memoryUsagePrevious;
echo $memoryUsageByQuery;
?>

In above code, We have not started fetching the records, still our PHP server will be bufferred with results.

This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer.

It also allows issuing further queries on the same connection while working on the result set which is not the case while using unbuffered mode.

The downside of the buffered mode is that larger result sets might require quite a lot memory.

The memory will be kept occupied untill all the references to the result set are unset or the result set was explicitly freed.

Unbufferred Mode:

Unbuffered MySQL queries execute the query and then wait for the data from the MySQL server to be fetched. 

Lets execute same code for unbufferred mode.

<?php
/**
 * PHP code
 */

$memoryUsagePrevious = memory_get_usage();
$sth = $db->query("SELECT * FROM table");
$memoryUsageLater = memory_get_usage();
$memoryUsageByQuery = $memoryUsageLater - $memoryUsagePrevious;
echo $memoryUsageByQuery;
?>

In above code, We have not started fetching the records, so our PHP server will not be bufferred with results.

The results are still on MySQL server which are yet to be fetched.

It will not allow issuing further queries on the same connection since the result set is not fetched while using unbuffered mode.

The advantage of the unbuffered mode is that larger result sets might not require quite a lot memory on the web server but will require same on DB server.

The memory on MySQL server will be kept occupied untill all the references to the result set are unset or the result set was explicitly freed.

Credits / Reference: https://www.php.net/manual/en/mysqlinfo.concepts.buffering.php