How to solve the problem that PHP uses mysql_query to query ultra-memory large result sets, phpmysql_query

How to solve the problem that PHP uses mysql_query to query ultra-memory large result sets, phpmysql_query

When mysql_query is used to query a large result set, a fatal error occurs because mysql_query queries all results and caches all result sets to the memory.

Mysql query also provides another query method. The function name is mysql_unbuffered_query. This function uses the result set to be operated immediately after the result is found and does not cache the result set to the memory, this avoids memory exceeding. However, the cost of using this method is that you cannot use methods such as getting the total line when querying, because this method returns results while querying. You cannot perform other operations on the same database link when using this method. To perform other operations, you must terminate the current operation first, release the results of all Uncached SQL queries, or instantiate a database connection and use the new link for other operations.

The following is a comparison between Cache Usage and no Cache Usage (the queried table contains more than 10 million rows of data ):

Function selecttest () {try {$ pdo = new PDO ("mysql: host = localhost; dbname = test", 'root', '123 '); // do not use the cache result set method // $ pdo-> setAttribute (PDO: MYSQL_ATTR_USE_BUFFERED_QUERY, false ); $ something = $ pdo-> prepare ('select * from test'); $ something-> execute (); echo 'memory size initially occupied :'. memory_get_usage (). "\ n"; $ I = 0; while ($ result = $ something-> fetch (PDO: FETCH_ASSOC) {$ I + = 1; if ($ I> 10) {break;} sleep (1); print_r ($ result); echo 'memory usage :'. memory_get_usage (). "\ n" ;}} catch (Exception $ e) {echo $ e-> getMessage ();}}

The above uses the method of caching all result sets. When you run this function, an out-of-memory error is reported, as shown below:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E: \ ProgramDevelopment \ RuntimeEnvironment \ xampp \ htdocs \ test. php on line 57

Call Stack:

0.0005 135392 1. {main} () E: \ ProgramDevelopment \ RuntimeEnvironment \ xampp \ htdocs \ test. php: 0
0.0005 135568 2. test-> selecttest () E: \ ProgramDevelopment \ RuntimeEnvironment \ xampp \ htdocs \ test. php: 86
0.0055 142528 3. PDOStatement-> execute () E: \ ProgramDevelopment \ RuntimeEnvironment \ xampp \ htdocs \ test. php: 57

The memory limit is exceeded when you execute $ something-> execute;

Run // $ pdo-> setAttribute (PDO: MYSQL_ATTR_USE_BUFFERED_QUERY, false). After this line of comment is removed, the result set is not cached. Running this function will output the following content:

Initial memory usage: 144808

Array([id] => 1[a] => v[b] => w[c] => i)

Memory used: 145544

Array([id] => 2[a] => b[b] => l[c] => q)

Memory used: 145544

Array([id] => 3[a] => m[b] => p[c] => h)

Memory used: 145536

Array([id] => 4[a] => j[b] => i[c] => b)

Memory used: 145536

Array([id] => 5[a] => q[b] => g[c] => g)

Memory used: 145536

As you can see, using the result set without caching to retrieve a row of Results occupies a very small amount of memory. In this way, the memory limit is exceeded.

Articles you may be interested in:

• PHP + Mysql + jQuery implement microblog program jQuery
• PHP + Mysql + jQuery enables dynamic display of information
• JQuery uses PHP + MySQL to implement a list of two-level linkage drop-down lists [instances]
• Solve PHP mysql_query execution timeout (Fatal error: Maximum execution time ...)
• Differences between mysqli_query parameter MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT in PHP
• Red-Blue (top-step) Voting code based on PHP + jQuery + MySql
• JQuery + Ajax + PHP + Mysql: display data by PAGE