PHP analysis Query MySQL large amount of data memory footprint

This article is mainly from the principles, manuals and source analysis in PHP query MySQL return a large number of results, memory footprint problem, while the use of the MySQL C API also involved.   Yesterday, some colleagues in the PHP discussion group (276167802 validation: CSL, if interested can join in to discuss) mentioned that he did a project because the MySQL query returned too many results (up to 100,000), resulting in insufficient PHP memory. So, he asks, is the data already in memory before executing the following code traversing the MySQL results returned? -  [PHP]   while ($row = Mysql_fetch_assoc ($result)) { //...  }     Of course, there are many ways to optimize this problem. However, on this issue, I first think that MySQL is a classic C/s (client/server, client/server) model, before traversing the result set, the underlying implementation may have all the data through the network (assuming the use of TCP/IP) read the Client's buffer, Another possibility is that the data is still in the server-side send buffer and not passed to the client.   before looking at the PHP and MySQL source, I noticed that there are two functions in the PHP manual that are similar:   [PHP]   mysql_query ()   Mysql_unbuffered_query () The literal meaning and description of the     two functions confirms my idea that when the previous function executes, it reads all the result sets from the server side to the client side of the buffer, and the second one does not, which means "unbuffered (not buffered)".   That is, if you execute a SQL statement that returns a large number of result sets with Mysql_unbuffered_query (), PHP memory is not occupied by the result set until the result is traversed. And with mysql_query () to execute the same statement, the function returned, PHP memory footprint will increase dramatically, immediately consume memory.   If you read the PHP code, you can see the similarities and differences in the implementation of these two functions:   [PHP]  //{{Proto resource mysql_query (string query [, int link_iden TifiER]   sends a SQL query to MySQL */  Php_function (mysql_query)   {  Php_mysql_do_query (internal_fun Ction_param_passthru, Mysql_store_result);  }  /*}}/    /{{{Proto resource Mysql_unbuffered_query (string query [, int link_identi Fier])   sends a SQL query to MySQL, without fetching and buffering-result rows/  php_function (Mysql_unbu Ffered_query)   {  Php_mysql_do_query (internal_function_param_passthru, Mysql_use_result);  }  / *}}/    Two functions are called php_mysql_do_query (), only the difference of the 2nd parameter, Mysql_store_result and Mysql_use_result. Then look at the implementation of Php_mysql_do_query ():   [php]  if (Use_store = = Mysql_use_result) {  Mysql_result=mysql_use_result (&mysql->conn);  } else {  Mysql_result=mysql_store_result (&mysql->conn);  }     Mysql_use_result () and Mysql_store_result () is the MySQL C API function, the difference of these two C API functions is that the latter the result set from the MySQL server to read all the client side, the former only read the result set of meta information.  Back to PHP, using Mysql_unbuffered_query (), you can avoid the immediate memory footprint. If the traversal process does not "PHP cache" The results (such as in an array), the entire execution process operates with 100,000 or millions or more data, but the memory used by PHP is always very small.   Hope this article for the vast number of PHP developers to help, thank you for reading this article.