PHP + MySQL: 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();
?>