How to solve the over-memory problem of using mysql_query to query very large result sets in PHP, phpmysql_query_PHP tutorial

How to solve the over-memory problem of PHP using mysql_query to query very large result sets? phpmysql_query

When using mysql_query to query very large result sets, a fatal error of exceeding the memory limit will occur. This is because mysql_query uses the method of querying all results and then caching the entire result set into memory.

Mysql query also provides another query method. The function name is mysql_unbuffered_query. This function uses the result set immediately after finding the result, and does not cache the result set into the memory, thus avoiding exceeding the limit. A memory situation occurs. However, the cost of using this method is that you cannot use methods such as getting the head office when querying, because this method returns results while querying. At the same time, you cannot perform other operations on the same database link when using this method. If you want to perform other operations, you must first terminate the current operation, release all result rows generated by uncached SQL queries, or re-instantiate a database. Connect and use the new link for other actions.

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

function selecttest()
{
try {
$pdo = new PDO("mysql:host=localhost;dbname=test", 'root', '123456');
// 不使用缓存结果集方式
// $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sth = $pdo->prepare('select * from test');
$sth->execute();
echo '最初占用内存大小：' . memory_get_usage() . "\n";
$i = 0;
while ($result = $sth->fetch(PDO::FETCH_ASSOC)) {
$i += 1;
if ($i > 10) {
break;
}
sleep(1);
print_r($result);
echo '占用内存大小：' . memory_get_usage() . "\n";
}
} catch (Exception $e) {
echo $e->getMessage();
}
}

The method used above is to cache all result sets. When running this function, an out-of-memory error will be reported, as shown below:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php on line 57

Call Stack:

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

Memory limit exceeded while executing $sth->execute();;

Removing the comment from // $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); will use the method of not caching the result set. Running this function will output the following content:

Initial memory size: 144808

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

Memory size occupied: 145544

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

Memory size occupied: 145544

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

Memory size occupied: 145536

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

Memory size occupied: 145536

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

Memory size occupied: 145536

As you can see, the memory occupied by obtaining a row of results without caching the result set is very small. This solves the problem of exceeding the memory limit.

Articles you may be interested in:

• PHP Mysql jQuery implements jQuery for publishing Weibo program
• PHP Mysql jQuery implements dynamic display of information
• jQuery combination PHP MySQL implements secondary linkage drop-down list [Example]
• Resolving PHP mysql_query execution timeout (Fatal error: Maximum execution time…)
• The difference between PHP’s mysqli_query parameters MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT
• Implementation of red and blue (upvote) voting code based on PHP jQuery MySql
• JQuery Ajax PHP Mysql implementation of paging display data example explanation