How can I optimize a PHP script that fetches 1M+ MySQL rows for reporting without exhausting memory? [duplicate]

I’m working on a PHP web application that generates reports from a MySQL table with over 1 million rows.

What I’m trying to do:

  • Fetch a large dataset and process it to generate a downloadable report (CSV format).

  • Improve speed and reduce memory usage.

What I’ve tried:

  • Pagination: I tried using LIMIT and OFFSET in chunks of 10,000.

  • Indexing: The main table is indexed on user_id and created_at.

  • Redis: I cache commonly accessed parts of the result set.

  • Output buffering: Used ob_flush() and flush() to stream output.

What I expected:

  • Significant reduction in memory usage and generation time.

  • Avoid timeouts and memory exhaustion.

What actually happened:

  • Still facing high memory usage (Allowed memory size exhausted) when generating large reports.

  • Execution time exceeds 30 seconds in some cases

set_time_limit(0);
ini_set('memory_limit', '512M');

$offset = 0;
$limit = 10000;
$fp = fopen('php://output', 'w');

while (true) {
    $query = "SELECT * FROM reports_table ORDER BY created_at ASC LIMIT $limit OFFSET $offset";
    $results = mysqli_query($conn, $query);

    if (mysqli_num_rows($results) == 0) break;

    while ($row = mysqli_fetch_assoc($results)) {
        fputcsv($fp, $row);
    }

    $offset += $limit;
    ob_flush();
    flush();
}