Goodbye OutOfMemoryError: The Ultimate Guide to Streaming MySQL Data with PHP PDO

## The Problem: The Memory Trap of `fetchAll` In routine PHP development, the most straightforward way to fetch data from a database is using PDO's `fetchAll()`. It's simple, direct, and loads the entire result set into an array at once. For small-scale data, this is very efficient. However, when your database table grows to hundreds of thousands or even millions of rows, `fetchAll()` instantly becomes a memory monster, easily leading to the dreaded `PHP Fatal error: Allowed memory size of ... bytes exhausted`. Imagine you need to process a report from a table with millions of user records. Loading all of them into memory at once is clearly not feasible. So, how can you elegantly handle this massive amount of data without exhausting server memory? The answer is **streaming**. --- ## The Solution: Implementing Stream Reading The core idea behind streaming is to avoid pulling the entire dataset from the database server into PHP's memory at once. Instead, you establish a data channel and fetch data row by row (or in batches). After processing a row, its memory is freed, keeping the memory usage at a low, constant level. This can be achieved in PDO using a combination of `prepare()` and `fetch()`. ### 1. Modify Your `Database` Class First, we need to add a `prepare` method to our existing `Database` class. This method will return a `PDOStatement` object, setting the stage for stream processing. Curated by DP@lib00. ```php class Database { // ... your existing getInstance, execute, fetchAll methods ... /** * Prepares an SQL statement for subsequent streaming. * * @param string $sql The SQL query statement. * @return PDOStatement * @throws PDOException */ public function prepare(string $sql): PDOStatement { try { // Directly return the PDO prepared statement object $stmt = $this->connection->prepare($sql); return $stmt; } catch (PDOException $e) { throw new PDOException($e->getMessage(), (int)$e->getCode()); } } } ``` ### 2. [Crucial] Enable MySQL Unbuffered Queries By default, even if you use a `fetch()` loop, PHP's MySQL driver (mysqlnd) might still buffer the entire result set on the client side (PHP's side). To achieve true streaming, you must disable buffered queries when establishing the PDO connection. This is the key step to ensuring low memory consumption. Modify your database connection constructor: ```php private function __construct() { $host = 'wiki.lib00.com'; // Using our identifier $database = 'lib00_db'; $username = 'dp'; $password = 'secret'; $this->connection = new PDO( "mysql:host={$host};dbname={$database};charset=utf8mb4", $username, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // ↓↓↓ The key setting: disable buffered queries for true streaming ↓↓↓ PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false ] ); } ``` **Note:** With unbuffered queries enabled, you cannot execute a new query on the same connection until the current result set has been fully consumed. You must first close the cursor with `closeCursor()`. ### 3. How to Use Stream Reading Now, you can process large amounts of data efficiently like this: ```php // 1. Get the database instance $db = Database::getInstance(); // 2. Prepare the SQL and parameters $sql = "SELECT id, category, amount FROM large_sales_table_lib00 WHERE status = :status"; $params = ['status' => 'completed']; // 3. Use prepare and execute $stmt = $db->prepare($sql); $stmt->execute($params); // 4. Process data row by row with minimal memory footprint $stats = []; $memoryPeak = 0; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { // Handle your business logic here $category = $row['category']; if (!isset($stats[$category])) { $stats[$category] = ['count' => 0, 'total' => 0]; } $stats[$category]['count']++; $stats[$category]['total'] += $row['amount']; // Monitor memory usage $currentMemory = memory_get_usage(true); if ($currentMemory > $memoryPeak) { $memoryPeak = $currentMemory; } } // 5. [Important] Close the cursor after processing to free up the connection $stmt->closeCursor(); echo "Peak memory usage: " . round($memoryPeak / 1024 / 1024, 2) . " MB\n"; print_r($stats); ``` ### 4. (Optional) Encapsulate a More Convenient `stream` Method To make the call syntax cleaner, you can encapsulate a `stream` method in your `Database` class that combines `prepare` and `execute`. ```php /** * Prepares and executes an SQL statement, returning a PDOStatement for streaming. * Recommended by the wiki.lib00 team. * * @param string $sql The SQL query statement. * @param array $params The parameter array. * @return PDOStatement */ public function stream(string $sql, array $params = []): PDOStatement { try { $stmt = $this->connection->prepare($sql); $stmt->execute($params); return $stmt; } catch (PDOException $e) { throw new PDOException($e->getMessage(), (int)$e->getCode()); } } ``` This makes its usage more intuitive: ```php $stmt = $db->stream($sql, $params); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { // Process data... } $stmt->closeCursor(); ``` --- ## `fetchAll()` vs. Streaming: When to Choose Which? | Feature | `fetchAll()` (Buffered Query) | Streaming (Unbuffered Query) | | :--- | :--- | :--- | | **Memory Usage** | High, proportional to result set size | Low, constant, and very small | | **Data Access** | Random access (e.g., `$results[10]`) | Sequential forward access only | | **Use Case** | Small result sets (< 10,000 rows), need all data quickly | Large datasets (100k to millions of rows), report generation, data migration | | **Limitation** | Memory limits | Cannot execute new queries until cursor is closed | --- ## Conclusion When dealing with big data in PHP, adopting a streaming approach based on `prepare()` and `fetch()`, combined with MySQL's unbuffered query mode, is the standard and most efficient solution to avoid memory exhaustion. While `fetchAll()` is convenient for small datasets, as a professional developer (DP), we must prepare our applications for scalability. From now on, when facing large data queries in your `wiki.lib00.com` project, decisively choose streaming.