Compare PHP to MySQL buffer query and no buffer query _php tips

About buffered queries and no buffer queries
There are two types of queries for MySQL clients:
buffered queries: will receive the results of the query and store them in the client's cache, and the request to obtain row records is only obtained locally.
(1) Advantage: You can move the "current row" pointer freely in the result set, which is easy to find because the result is in the presence of the client.
(2) Disadvantage: Additional memory is required to store these result sets, and a large amount of memory is required, and the function used in PHP to run the query will be returned until all results are received.

No buffer queries: restricts access to query results in strict order. But he doesn't need extra memory to store the entire result set. You can start fetching when the MySQL server starts to return the value and process or display the data rows. When using a buffer-free result set, you must use the Mysql_fetch_row function to get the data rows, or close the result set with the Mysql_free_result function before sending any other commands to the server.

What kind of query is good? the best based on the specific circumstances, no buffer query in the large result set for you to save a lot of temporary memory, and the query does not need to sort, PHP in the MySQL database is actually still processing when you can get the first data row.
Cached queries are easy to find, and it can provide a comprehensive search for acceleration. Because each individual query is fast-read, MySQL quickly gets the result set and holds it in memory, rather than keeping the query available when processing PHP code.
Another limit for a buffer-free query is that you will not be able to send any commands to the server until all data rows are read or the result set is released with Mysqli_free_result.

php+mysql buffer Query and no buffer query
PHP MySQL Query (mysqli,pdo_mysql) uses buffering mode by default.
This means that the query results will be transferred from MySQL to PHP process memory
You can then count the number of rows in the result set and move the result set pointer.
Buffer mode, if the result set is large, then the PHP process will also occupy a lot of memory,
Until the result set is unset or free.

Store_result is used in buffering mode, and all results are stored in the PHP process at once:

Mysqli::query mysqli_store_result 
mysqli::store_result 
mysqli_stmt::store_result 

If PHP's MySQL database driver is libmysqlclient, then Memory_limit cannot count the memory consumed by the result set.
Unless the result set has been assigned to a PHP variable, the underlying use of MYSQLND as a driver can be counted (PHP uses mysqlnd from the bottom of 5.4).
Queries that are executed in no buffer mode will return a resource resource reference, and the results of the query in MySQL await PHP access.
In no buffer mode, the PHP process consumes very little memory, but increases the load on the MySQL server.
No other query request can be sent under the current database connection until PHP retrieves all the results.

Use_result indicates no buffer query:

Mysqli::query Mysqli_use_result 
Mysqli::use_result 

Summarize:
Use a buffered query (default) when the result set is small, or you need to get the number of result set rows before reading all rows.
When the result set is very large, use no buffer query to avoid the PHP process to occupy a large amount of memory.

$rs = $mysqli->query ("SELECT * from City", Mysqli_use_result); 
$pdo->setattribute (Pdo::mysql_attr_use_buffered_query, false); 
$rs = $pdo->query ("SELECT * from City"); 

By default, MYSQLI_STMT's Select query results will remain on the MySQL server, waiting for the fetch method to retrieve the records into the PHP program, which can degrade performance but save memory.
If you need to process all the records, you can call Mysqli_stmt::store_result and pass all the results back to the PHP program at once,
This is more efficient, and can reduce the burden on the MySQL server, although the memory footprint will be more.
If you get the number of records found by the SELECT statement, you can use MYSQLI_STMT:: $num _rows to get it.
This property can only be used if the Mysqli_stmt::store_result method is executed in advance, and all query results are passed back to the PHP program.
Contrast Mysqli_result:: $num _rows is not without this restriction.
Close mysqli_stmt::store_result with Mysqli_stmt::free_result:

$stmt->store_result (); 
echo $stmt->num_rows; 
$stmt->free_result (); 

Mysqli_stmt::store_result can make mysqli_stmt::fetch more efficient, but it also needs to be explicitly closed with Mysqli_stmt::free_result.
You can use Mysqli_stmt::get_result to get the result set object $result, and then Mysqli_result::fetch_all get the query array $results:

$result = $stmt->get_result (); 
$results = $result->fetch_all (MYSQLI_ASSOC); 

To organize the relevant parameters:

mysqli::query//executes SQL, successfully returns Mysqli_result (Select,show,describe action) object, or True (other operation), failure returns false. Close with Mysqli::close.
Mysqli::p repare//preprocessing SQL, successfully returned statement object, failed to return false.
mysqli_stmt::execute//Execute SQL. Close with Mysqli_stmt::close.
mysqli_stmt::store_result//Retrieve all query results (select,show,describe,explain) to PHP, optional. Close with Mysqli_stmt::free_result.
mysqli_stmt::bind_result//binds the prepare and execute structures to the variables and then outputs or assigns them to the Mysqli_stmt::fetch.
mysqli_stmt::fetch//each time a result set is returned, assign a value to the Mysqli_stmt::bind_result bound variable.
mysqli_stmt::get_result//Gets the result object and then calls Mysqli_result::fetch_all to return an array of result sets. MYSQLND is available under.
mysqli_result::fetch_all//returns an array of result sets (Mysqli_num (default), Mysqli_assoc,mysqli_both), with Mysqli_result:: Close closes. Mysqlnd is available
under. mysqli_result::fetch_array//each time a result set is returned, containing a one-dimensional array of numbers and an associative array.
mysqli_result::fetch_assoc//each time a result set is returned, a one-dimensional associative array.
mysqli_result::fetch_row//each time a result set is returned, a one-dimensional array of numbers.