PHP uses mysql_query to query super-large result set super memory solution

Source: Internet
Author: User
Tags mysql query
When you use mysql_query to query a large result set, there is a fatal error that exceeds the memory limit, because Mysql_query uses a way to query all the results and then cache all the result sets in memory.

The MySQL query also provides another way of querying the function named Mysql_unbuffered_query, which uses the result set immediately after the results are detected, and does not cache the result set into memory, thus avoiding the memory-exceeding situation. But the cost of using this method is to use methods such as getting the head row when you can no longer query, because this method queries the side to return the results. While using this method, you cannot perform other operations on the same database link, you must terminate the current operation when you want to perform other operations, release the resulting rows from all non-cached SQL queries, or re-instantiate a database connection and use the new link for additional operations.

The following is a comparison of using the cache and not using the cache (there are more than 10 million rows of data in the table being queried):

function Selecttest ()    {        try {            $pdo = new PDO ("Mysql:host=localhost;dbname=test", ' Root ', ' 123456 ');//            do not use cached result set mode//            $pdo->setattribute (Pdo::mysql_attr_use_buffered_query, false);            $sth = $pdo->prepare (' SELECT * from test ');            $sth->execute ();            Echo ' initially takes up memory size: '. Memory_get_usage (). "\ n";            $i = 0;            while ($result = $sth->fetch (PDO::FETCH_ASSOC)) {                $i + = 1;                if ($i >) {break                    ;                }                Sleep (1);                Print_r ($result);                Echo ' occupies memory size: '. Memory_get_usage (). "\ n";            }        } catch (Exception $e) {            echo $e->getmessage ();        }    }

The above uses the way that all result sets are cached, and the memory error is reported when the function is run, as follows:

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 executing $sth->execute ();

Will//$pdo->setattribute (Pdo::mysql_attr_use_buffered_query, false); When the comment of this line is removed, it will be run with no cache result set, and the function will output the following:

Initial Memory Size: 144808Array (    [id]    = 1 [A] = v    [b] + w    [C] + i) occupies memory size: 145544Array (    [id] =  > 2    [A] = b    [b] + L    [c] = + Q) take up memory size: 145544Array (    [id] + 3    [A] = [    b] = = P    [c] + h) takes up memory size: 145536Array (    [id] + 4    [a] + j    [b] = i    [c] + B) take up memory size: 14553 6Array (    [id] + 5    [a] + q    [b] = = G    [c] = = g) Occupy Memory size: 145536

You can see that the amount of memory used to get a row of results without caching the result set is minimal. This ends the problem of exceeding the memory limit.

  • 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.