How to use mysql_query to query super-memory large result sets in php

Source: Internet
Author: User
When php uses mysql_query to query super-large result sets and then uses mysql_query to query ultra-large result sets, a fatal error occurs that exceeds the memory limit, this is because mysql_query uses the method of Querying all results and then caching 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\test.php on line 57Call Stack:    0.0005     135392   1. {main}() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php:0    0.0005     135568   2. test->selecttest() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php:86    0.0055     142528   3. PDOStatement->execute() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\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:

Initially occupied memory size: 144808 Array ([id] => 1 [a] => v [B] => w [c] => I) occupied memory size: 145544 Array ([id] => 2 [a] => B [B] => l [c] => q) memory usage: 145544 Array ([id] => 3 [a] => m [B] => p [c] => h) memory usage: 145536 Array ([id] => 4 [a] => j [B] => I [c] => B) memory usage: 145536 Array ([id] => 5 [a] => q [B] => g [c] => g) memory usage: 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.

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: info-contact@alibabacloud.com 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.