search
HomeBackend DevelopmentPHP TutorialHow 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

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1110092.htmlTechArticleHow to solve the over-memory problem of using mysql_query to query very large result sets in PHP. When phpmysql_query is used to query very large result sets, it will A fatal error occurred that exceeded the memory limit, this...
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How can you protect against Cross-Site Scripting (XSS) attacks related to sessions?How can you protect against Cross-Site Scripting (XSS) attacks related to sessions?Apr 23, 2025 am 12:16 AM

To protect the application from session-related XSS attacks, the following measures are required: 1. Set the HttpOnly and Secure flags to protect the session cookies. 2. Export codes for all user inputs. 3. Implement content security policy (CSP) to limit script sources. Through these policies, session-related XSS attacks can be effectively protected and user data can be ensured.

How can you optimize PHP session performance?How can you optimize PHP session performance?Apr 23, 2025 am 12:13 AM

Methods to optimize PHP session performance include: 1. Delay session start, 2. Use database to store sessions, 3. Compress session data, 4. Manage session life cycle, and 5. Implement session sharing. These strategies can significantly improve the efficiency of applications in high concurrency environments.

What is the session.gc_maxlifetime configuration setting?What is the session.gc_maxlifetime configuration setting?Apr 23, 2025 am 12:10 AM

Thesession.gc_maxlifetimesettinginPHPdeterminesthelifespanofsessiondata,setinseconds.1)It'sconfiguredinphp.iniorviaini_set().2)Abalanceisneededtoavoidperformanceissuesandunexpectedlogouts.3)PHP'sgarbagecollectionisprobabilistic,influencedbygc_probabi

How do you configure the session name in PHP?How do you configure the session name in PHP?Apr 23, 2025 am 12:08 AM

In PHP, you can use the session_name() function to configure the session name. The specific steps are as follows: 1. Use the session_name() function to set the session name, such as session_name("my_session"). 2. After setting the session name, call session_start() to start the session. Configuring session names can avoid session data conflicts between multiple applications and enhance security, but pay attention to the uniqueness, security, length and setting timing of session names.

How often should you regenerate session IDs?How often should you regenerate session IDs?Apr 23, 2025 am 12:03 AM

The session ID should be regenerated regularly at login, before sensitive operations, and every 30 minutes. 1. Regenerate the session ID when logging in to prevent session fixed attacks. 2. Regenerate before sensitive operations to improve safety. 3. Regular regeneration reduces long-term utilization risks, but the user experience needs to be weighed.

How do you set the session cookie parameters in PHP?How do you set the session cookie parameters in PHP?Apr 22, 2025 pm 05:33 PM

Setting session cookie parameters in PHP can be achieved through the session_set_cookie_params() function. 1) Use this function to set parameters, such as expiration time, path, domain name, security flag, etc.; 2) Call session_start() to make the parameters take effect; 3) Dynamically adjust parameters according to needs, such as user login status; 4) Pay attention to setting secure and httponly flags to improve security.

What is the main purpose of using sessions in PHP?What is the main purpose of using sessions in PHP?Apr 22, 2025 pm 05:25 PM

The main purpose of using sessions in PHP is to maintain the status of the user between different pages. 1) The session is started through the session_start() function, creating a unique session ID and storing it in the user cookie. 2) Session data is saved on the server, allowing data to be passed between different requests, such as login status and shopping cart content.

How can you share sessions across subdomains?How can you share sessions across subdomains?Apr 22, 2025 pm 05:21 PM

How to share a session between subdomains? Implemented by setting session cookies for common domain names. 1. Set the domain of the session cookie to .example.com on the server side. 2. Choose the appropriate session storage method, such as memory, database or distributed cache. 3. Pass the session ID through cookies, and the server retrieves and updates the session data based on the ID.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!