If you wish to process a very large number of rows from a database then it may be prohibitively expensive to load them all into memory first. Laravel’s chunk() function may look like the solution however this performs a query for each chunk and is therefore extremely slow.

The ideal solution is to disable query buffering and thereby only ever have a single row in memory at once. Note that this option is specific to the MySQL PDO driver.

// Disable query buffering on the connection.
$connection = DB::connection();
$pdo = $connection->getReadPdo();
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// Build your query.
$query = DB::table('users')->select('*')->orderBy('name');

// Execute the query and iterate over each row.
foreach ($connection->cursor($query->toSql(), $query->getBindings()) as $row) {
    var_dump($row);
}

// Re-enable query buffering.
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

This code first fetches the PDO connection to the database and disables query buffering. You can use Laravel’s query builder as normal to construct your query. The connection’s cursor() function is used to iterate over every row.

Note that you must re-enable query buffering after you have finished, or later queries will be affected.

Unbuffered HTTP Output

If you wish to return the data from an HTTP endpoint, for example as a data feed, then you must also make sure that the output is not buffered. This is accomplished by using the stream() or streamDownload() method on the response.

streamDownload() takes four parameters:

1. A callback that produces the page output via echo/print/etc.
2. The filename, which defaults to the route name if null.
3. Additional HTTP headers, used here to set the Content-Type.
4. The HTTP disposition, which defaults to attachment.

The example below shows how to add CSV output to your controller class.

use Illuminate\Http\Request;
use Symfony\Component\HttpFoundation\StreamedResponse;

class MyApiController extends Controller
{
    public function csv(Request $request): StreamedResponse
    {
        return response()->streamDownload([$this, 'outputCsv'], null, ['Content-Type' => 'text/csv']);
    }

    private function outputCsv(): void
    {
        // Disable query buffering on the connection.
        $connection = DB::connection();
        $pdo = $connection->getReadPdo();
        $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

        // Build your query.
        $query = DB::table('users')->select('name, email')->orderBy('name');

        // Execute the query and iterate over each row.
        foreach ($connection->cursor($query->toSql(), $query->getBindings()) as $row) {
            echo $row->name . ',' . $row->email. "\n";
        }

        // Re-enable query buffering.
        $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);    }
}