Difference between buffered and unbuffered queries

Question

I was always under the impression the difference, in simple terms, between PHP/MySQL buffered and unbuffered queries is that buffered (the default) loads all the data into your results set variable and then you can start using them whereas unbuffered loads a row at a time.

Say you ran SELECT * FROM sometable and then did $result = $db->query($query);, $result would contain all the rows and supplementary information such as the number of rows. So if you did it on a 100MB database you'd expect $result to take up ~100MB if there were no indices on there).

However, I came across this SO overflow question part of which says of buffered queries:

[The] result will contain some buffer of rows that is implementation dependent. It might be 100 rows or more or less. All columns are returned for each row; As you fetch more rows eventually the client will ask the server for more rows. This may be when the client runs out or it may be done preemptively.

Is this right, is there really still some buffering going on? If that is the case, do we generally not need to worry about PHP running out of memory when dealing with large result sets? It's odd because I have been running some test buffered queries on a 40MB test table and PHP always reports a peak memory usage of ~5MB.

Finally, as a rule of thumb, when do you choose unbuffered over buffered? Can you please provide an example?

(I am using MySQLi, by the way. I assume the principal is the same).

I have read a bit more now and am even more confused. On http://php.net/manual/en/mysqli.quickstart.statements.php it says

On After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query() combines statement execution and result set buffering.

PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

And on http://php.net/manual/en/mysqli-result.fetch-all.php it says:

As mysqli_fetch_all() returns all the rows as an array in a single step, it may consume more memory than some similar functions such as mysqli_fetch_array(), which only returns one row at a time from the result set. Further, if you need to iterate over the result set, you will need a looping construct that will further impact performance. For these reasons mysqli_fetch_all() should only be used in those situations where the fetched result set will be sent to another layer for processing.

This seems somewhat contradictory. What's the difference between “client-side result set buffering” and “consuming result sets”? One says they're held in client memory and the other says read row by row. If the whole thing is buffered to PHP why does that last quote says that if you return all the rows as an array in a single step it may consume more memory?

Answer 1

See: http://php.net/manual/en/mysqlinfo.concepts.buffering.php

Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".

Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results.

Buffered queries are default.

Unbuffered Example:

<?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();
?>

Answer 2

When you execute an SQL query from PHP, the MySQL server will reply with a status message. This tells PHP (more accurately the client library, e.g. mysqlnd) whether the query produced a result set or not. If a result set is present, then MySQL is expecting mysqlnd to request every row one by one. The results are not transferred as a whole, but instead in packets. An EOF_Packet is sent to indicate the end of the result set.

PHP can retrieve all rows of the result set and store them in RAM. This is a buffered result set. The data is "buffered" in PHP memory internally for you to read using mysqli or PDO interface.

PHP can be instructed to NOT store the results in memory at all, and instead fetch every single row directly from the MySQL connection. This is an unbuffered result set. Only one row is stored at any given time in PHP memory.

Buffered queries have the following features:

• You can freely move the pointer to any row you wish
• You know how many rows there are
• All rows can be transferred to a PHP array
• The result set can be iterated multiple times without re-executing the query
• The connection can be used to execute another query while processing the results from the previous one

Unbuffered MySQL queries have the following features:

• Reduce PHP memory allocation, but increase the load on the MySQL server
• Cannot be rewound or iterated multiple times
• You cannot execute any other query on the same connection until the last row is fetched

---

When to choose unbuffered over buffered

The default mode is usually buffered. Only mysqli prepared statements return unbuffered result sets by default. You can ask mysqlnd to buffer them by calling $stmt->store_result() or by fetching the mysqli_result object using $stmt->get_result().

Due to the additional complexity and limitations, buffered queries should be the default choice for most use cases. Unbuffered queries may be used in highly specialized circumstances where the PHP memory limit won't allow fetching huge amounts of data or where PHP is just an intermediary layer, e.g. fetching results to a file. There should be practically no difference in performance otherwise.

In a web environment, you want to use buffered queries 100% of the time. Unbuffered queries demand exceptional circumstances and a more complex codebase.