Buffered Vs Unbuffered Queries in PHP

Today I am going to share one quick article about buffered and unbuffered queries in MySQL. In this quick article, I am going to explain an information and usage about the buffered and unbuffered queries.

The MySQL client supports two types of queries one is buffered and other is unbuffered queries.so i am going to explain about buffered and unbuffered queries:

What is Buffered query?

Buffered queries will retrieve the query results and store the queries in memory on the client side and subsequent calls to get rows will simply spool through local memory.

Main Advantage with the buffered queries is that you can Buffered queries. Also, buffered queries have the advantage that you can search in queries which means that you can freely move the “current row” pointer around in the result set because it is all in the client.

Whereas the disadvantage of Buffered queries is that extra memory/space is required to store the result set which can be very large and that the PHP function used to run the query does not return until all the results have been retrieved.

What is Unbuffered query?

However, The Unbuffered queries have the limitation to a strict sequential access of the results but it does not require any extra space or memory for storing the whole result set.You can fetch and process or display rows when MySQL server returns to the client.

Unbuffered queries save a lot of memories when the result set is large and when the query does not require any kind of sorting. Buffered queries are convenient because of the searching feature and it also provide a speedup in queries.Because each individual query will be completed faster the MySQL extension will give the result set immediately and store the result sets in memory instead of keeping the query active while processing PHP code.

One Other limitation with Unbuffered queries is that you will not be able to send any command to the server unless all rows are read or the result set is freed by mysqli_free_result.

Let’s have a look at one example:

SYNTAX:

Here in mysqli_query function, third optional parameter mode will be buffered or unbuffered.

When we use mysqli_query function and execute the query the memory on the client side is allocated to retrieve the complete result set. To use unbuffered resultset, you have to specify the optional parameter MYSQLI_USE_RESULT otherwise MYSQLI_STORE_RESULT.You would like to read about PHP Interface.

Don’t forget to subscribe to our Email newsletter for more guides and tips.Do let me know what have you learned from this article? Is this article helpful to you to understand buffered and unbuffered queries?