Compare the buffer query and no buffer query of MySQL in PHP, phpmysql

Compare the buffer query and no buffer query of MySQL in PHP, phpmysql

About cache query and unbuffered Query
The MySQL client has two types of queries:
Buffer query:The query results will be received and stored in the client cache, And the next request to obtain Row Records will only be obtained locally.
(1) Advantage: You can freely move the "current row" pointer in the result set, which is easy to find because the result is stored on the client.
(2) Disadvantages: Additional memory is required to store these result sets, and a large amount of memory is required. In addition, the function used to run the query in php will not return the value until all results are received.

No buffer query:It will restrict you from accessing the query results in strict order. However, he does not need additional memory to store the entire result set. You can obtain and process or display data rows when the MySQL server starts to return values. When using a result set without buffering, you must use the mysql_fetch_row function to obtain the data rows, or use the mysql_free_result function to disable the result set before sending any other commands to the server.

Which type of query is better? It is best to choose based on the specific situation. When the result set is large, no buffer queries save a lot of temporary memory, and the query does not need to be sorted, php can actually obtain the first data row when the MySQL database is still processing.
Cache query is easy to find, and it provides a comprehensive search acceleration. Because each individual query will end with fast reading, mysql's quick retrieval result set will coexist in the memory, rather than making the query available when processing PHP code.
Another unlimited query restriction is that you cannot send any commands to the server until all data rows are read or the result set is released using mysqli_free_result.

PHP + MySQL buffer query and no buffer Query
PHP MySQL query (mysqli, pdo_mysql) uses the buffer mode by default.
That is to say, the query results will be transmitted from MySQL to the PHP process memory at a time,
You can calculate the number of rows in the result set and move the result set pointer.
In buffer mode, if the result set is large, the PHP process also occupies a large amount of memory,
Until the result set is unset or free.

Store_result is used in the buffer mode. All results are stored in the PHP process at one time:

mysqli::query MYSQLI_STORE_RESULT mysqli::store_result mysqli_stmt::store_result 

If libmysqlclient is used at the underlying layer of the MySQL database driver in PHP, memory_limit cannot count the memory occupied by the result set,
Unless the result set has been assigned to the PHP variable, statistics can be collected if mysqlnd is used as the driver at the underlying layer (mysqlnd is used by default in PHP from 5.4 ).
In the cache-free mode, a resource reference is returned for queries executed. The query results in MySQL are waiting for PHP to obtain.
In the buffer-free mode, the PHP process occupies a small amount of memory, but it will increase the load on the MySQL server.
No other query requests can be sent under the current database connection before PHP Retrieves all results.

Use_result indicates no buffer query:

mysqli::query MYSQLI_USE_RESULT mysqli::use_result 

Summary:
When the result set is not large, or the number of rows in the result set needs to be obtained before all rows are read, the buffer query is used (default ).
When the result set is large, queries without buffering are used to prevent PHP processes from occupying 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, the SELECT query results of mysqli_stmt will be left on the MySQL server, waiting for the fetch method to return records one by one back to the PHP program. This will reduce performance, but save memory.
If you need to process all the records, you can call mysqli_stmt: store_result to upload all the results to the PHP program at one time,
This is more efficient and can reduce the burden on the MySQL server, although the memory usage will be more.
You can use mysqli_stmt: $ num_rows to obtain the number of records found in the SELECT statement.
This attribute can be used only when the mysqli_stmt: store_result method is executed in advance and all query results are returned to the PHP program.
Compared with mysqli_result: $ num_rows, this restriction is not imposed.
Use mysqli_stmt: free_result to disable mysqli_stmt: store_result:

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

Mysqli_stmt: store_result can make mysqli_stmt: fetch more efficient, but you also need to use mysqli_stmt: free_result to explicitly close it.
You can use mysqli_stmt: get_result to get the result set object $ result, and then mysqli_result: fetch_all to get the query array $ results:

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

Sort out the relevant parameters:

Mysqli: query // run the SQL statement. The mysqli_result (SELECT, SHOW, DESCRIBE operation) object or TRUE (other operations) object are returned successfully. If the operation fails, FALSE is returned. close with mysqli: close. mysqli: prepare // pre-process the SQL statement. The statement object is returned successfully. Otherwise, FALSE is returned. mysqli_stmt: execute // execute SQL. close with mysqli_stmt: close. mysqli_stmt: store_result // Retrieves all query results (SELECT, SHOW, DESCRIBE, EXPLAIN) to PHP. Optional. close it with mysqli_stmt: free_result. mysqli_stmt: bind_result // bind the structure generated by prepare and execute to the variable, and then output or assign values to these variables in mysqli_stmt: fetch. mysqli_stmt: fetch // One of the returned result sets is assigned to the variable bound to mysqli_stmt: bind_result. mysqli_stmt: get_result // obtain the result object, and then call mysqli_result: fetch_all to return the number of result sets. available under mysqlnd. mysqli_result: fetch_all // returns a number group of result sets (MYSQLI_NUM (default), MYSQLI_ASSOC, MYSQLI_BOTH), and closes with mysqli_result: close. available under mysqlnd. mysqli_result: fetch_array // One Of The result sets returned each time. It contains a one-dimensional array of numbers and associated arrays. mysqli_result: fetch_assoc // One Of The result sets returned each time, that is, a one-dimensional associated array. mysqli_result: fetch_row // One Of The result sets returned each time, that is, a one-dimensional numeric array.