title: Difference between buffered and unbuffered queries
body: <p>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 <em>then</em> you can start using them whereas unbuffered loads a row at a time.</p>
  <p>Say you ran <code>SELECT * FROM sometable</code> and then did <code>$result = $db-&gt;query($query);</code>, <code>$result</code> 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 <code>$result</code> to take up ~100MB if there were no indices on there).</p>
  <p>However, I came across this <a href="https://stackoverflow.com/questions/2215616/how-do-php-mysql-database-queries-work-exactly">SO overflow question</a> part of which says of buffered queries:</p>
  <blockquote>
  <p>[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.</p>
  </blockquote>
  <p>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.</p>
  <p>Finally, as a rule of thumb, when do you choose unbuffered over buffered? Can you please provide an example?</p>
  <p>(I am using MySQLi, by the way. I assume the principal is the same).</p>
  <p>I have read a bit more now and am even more confused. On <a href="http://php.net/manual/en/mysqli.quickstart.statements.php" rel="nofollow noreferrer">http://php.net/manual/en/mysqli.quickstart.statements.php</a> it says</p>
  <blockquote>
  <p>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.</p>
  <p>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.</p>
  </blockquote>
  <p>And on <a href="http://php.net/manual/en/mysqli-result.fetch-all.php" rel="nofollow noreferrer">http://php.net/manual/en/mysqli-result.fetch-all.php</a> it says:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>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?</p>