Analysis of memory usage when PHP queries a large amount of MySQL data

Source: Internet
Author: User
This article mainly describes the memory usage when MySQL returns a large number of results in PHP based on principles, manuals, and source code analysis. it also involves the use of MySQLCAPI. yesterday, a colleague mentioned in the PHP discussion group that a project he created had too many results (up to 0.1 million results) returned by MySQL queries, resulting in insufficient PHP memory. therefore, he asked, before executing the following code to traverse the returned MySQL results, is the data already in the memory? -
Copy codeThe code is as follows:
While ($ row = mysql_fetch_assoc ($ result )){

Of course, there are many optimization methods for this problem. however, for this question, I first thought that MySQL is a classic C/S (Client/Server, Client/Server) model. before traversing the result set, the underlying implementation may have read all the data to the Client's buffer through the network (assuming TCP/IP is used, that is, the data is still in the sending buffer of the Server and is not sent to the Client.
Before viewing the source code of PHP and MySQL, I noticed that the PHP Manual has two functions similar to the following:
Copy codeThe code is as follows:
Mysql_query ()
Mysql_unbuffered_query ()

The literal meanings and descriptions of the two functions confirm my idea. when the previous function is executed, all the result sets will be read from the Server to the Client buffer, and the other functions will not, this is what "unbuffered (unbuffered)" means.
That is to say, if mysql_unbuffered_query () is used to execute an SQL statement that returns a large number of result sets, the PHP memory is not occupied by the result set before traversing the results. if you use mysql_query () to execute the same statement, the PHP memory usage increases dramatically when the function returns, consuming the memory immediately.
If you read the PHP code, you can see the similarities and differences between the two functions:
Copy codeThe code is as follows:
/* {Proto resource mysql_query (string query [, int link_identifier])
Sends an SQL query to MySQL */
PHP_FUNCTION (mysql_query)
/* {Proto resource mysql_unbuffered_query (string query [, int link_identifier])
Sends an SQL query to MySQL, without fetching and buffering the result rows */
PHP_FUNCTION (mysql_unbuffered_query)

Both functions call php_mysql_do_query (), but the difference is only 2nd. MYSQL_STORE_RESULT and MYSQL_USE_RESULT. then, let's look at the implementation of php_mysql_do_query:
Copy codeThe code is as follows:
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 () are MySQL c api functions. The difference between these two c api functions is that the latter reads all the result sets from the MySQL Server to the Client, the former only reads the meta information of the result set.
Back to PHP, use mysql_unbuffered_query () to avoid immediate memory usage. if the result is not cached in PHP during the traversal process (such as in an array), although the entire execution process operates on 100,000 or millions of data records or more, however, the memory occupied by PHP is always very small.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.