Stop Using `chunk()`: The Performance Trap with Large Datasets

When dealing with large database tables in Laravel—such as processing millions of invoices, sending batch notifications, or running analytical exports—running Model::all() or Model::get() will quickly crash your application with an Allowed memory size exhausted error. To solve this, developers learn to break large datasets into smaller batches using Laravel's chunk() method.

However, when your table scales past hundreds of thousands or millions of records, you might notice something alarming: the chunk() method starts running progressively slower with every single iteration, eventually causing CPU spikes, timeouts, and unexpected skipped records.

In this article, we will understand the MySQL OFFSET performance trap behind standard chunk(), why chunkById() is up to 100x faster for large datasets, and how to use it properly in your Laravel applications.

The Problem: The MySQL OFFSET Performance Trap

To understand why chunk() slows down, let's look at the underlying SQL queries Laravel executes when you use it.

use App\Models\User;

User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

Behind the scenes, Laravel's chunk() relies on standard SQL pagination using LIMIT and OFFSET:

-- Chunk 1
SELECT * FROM `users` ORDER BY `users`.`id` ASC LIMIT 1000 OFFSET 0;

-- Chunk 2
SELECT * FROM `users` ORDER BY `users`.`id` ASC LIMIT 1000 OFFSET 1000;

-- Chunk 500
SELECT * FROM `users` ORDER BY `users`.`id` ASC LIMIT 1000 OFFSET 500000;

-- Chunk 1000
SELECT * FROM `users` ORDER BY `users`.`id` ASC LIMIT 1000 OFFSET 1000000;

Why High OFFSETs Kill Performance

In MySQL and PostgreSQL, the database engine cannot simply jump directly to row number 1,000,000. To return rows starting at OFFSET 1000000, MySQL must scan all 1,000,000 rows, load them into memory, discard them, and then return only the 1,000 rows requested by LIMIT 1000.

As your script iterates further into the dataset:

The first few chunks take a few milliseconds.

By chunk 500, each query takes several seconds.

By chunk 1,000, queries start locking tables and timing out.

The Hidden Bug: Updating Records Inside chunk()

There is an even worse side-effect when you update rows inside a chunk() loop:

// DANGEROUS: Skips records!
User::where('processed', false)->chunk(100, function ($users) {
    foreach ($users as $user) {
        $user->update(['processed' => true]);
    }
});

When chunk 1 finishes, those 100 records now have processed = true. When chunk 2 executes with OFFSET 100, the original dataset has shifted because the first 100 records no longer match the WHERE clause. As a result, 50% of your records are silently skipped!

The Solution: How chunkById() Works

Laravel's chunkById() solves both problems by replacing OFFSET pagination with keyset pagination (seek method) using primary keys.

use App\Models\User;

User::chunkById(1000, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

Instead of calculating an increasing OFFSET, Laravel tracks the highest ID from the previous chunk and uses a direct WHERE id > ? clause:

-- Chunk 1
SELECT * FROM `users` WHERE `users`.`id` > 0 ORDER BY `users`.`id` ASC LIMIT 1000;

-- Chunk 2 (last ID in chunk 1 was 1000)
SELECT * FROM `users` WHERE `users`.`id` > 1000 ORDER BY `users`.`id` ASC LIMIT 1000;

-- Chunk 1000 (last ID in chunk 999 was 1000000)
SELECT * FROM `users` WHERE `users`.`id` > 1000000 ORDER BY `users`.`id` ASC LIMIT 1000;

Because the primary key id has a B-tree index, MySQL jumps straight to the exact index position with $O(\log N)$ complexity. The 1,000th chunk runs just as fast as the 1st chunk—consistently executing in milliseconds regardless of whether your table has 10,000 rows or 50,000,000 rows.

Real-World Practical Example

Let's look at an Artisan command processing monthly account statements for millions of active users:

namespace App\Console\Commands;

use App\Models\User;
use App\Jobs\GenerateMonthlyStatementJob;
use Illuminate\Console\Command;

class DispatchMonthlyStatements extends Command
{
    protected $signature = 'statements:dispatch';
    protected $description = 'Dispatch statement generation for all active users';

    public function handle(): int
    {
        $this->info('Dispatching statements...');

        User::where('is_active', true)
            ->chunkById(2000, function ($users) {
                foreach ($users as $user) {
                    dispatch(new GenerateMonthlyStatementJob($user));
                }
            });

        $this->info('All statement jobs dispatched successfully!');
        return Command::SUCCESS;
    }
}

Working with Custom Primary Keys or Columns

By default, chunkById() expects an auto-incrementing integer column named id. If your model uses a different column name or alias, you can pass custom column and alias names as parameters:

// Signature: chunkById($count, callable $callback, $column = null, $alias = null)
Order::chunkById(1000, function ($orders) {
    // Process orders
}, 'order_id');

Performance Comparison: chunk() vs chunkById()

Criteria	chunk()	chunkById()
SQL Pagination Method	`LIMIT 1000 OFFSET X`	`WHERE id > X ORDER BY id ASC LIMIT 1000`
Query Time (Deep Records)	Degrades exponentially (slows to seconds).	Constant speed (instant index lookup).
Safe for In-Place Updates	❌ No (skips rows when updating matched criteria).	✅ Yes (never skips rows).
Primary Key Requirement	None.	Requires an indexed, sequential column (like auto-increment ID).

Alternative for Lazy Iteration: lazyById()

If you prefer using PHP Generators to stream records one by one without nested callback closures, Laravel also provides lazyById():

use App\Models\User;

// Fetches 1,000 rows into memory at a time under the hood via chunkById
foreach (User::lazyById(1000) as $user) {
    $user->update(['last_audited_at' => now()]);
}

Important Things to Remember

Auto-Incrementing Column Required: chunkById() requires an indexed, sequentially sortable column (typically an auto-incrementing integer primary key). If your table uses non-sequential UUIDs (UUIDv4) as primary keys, standard keyset comparison with > will not order sequentially. In that scenario, use ULIDs, ordered UUIDs (UUIDv7), or sequential timestamp columns.

Overriding ORDER BY: chunkById() automatically appends an ORDER BY id ASC clause. Do not add custom orderBy() clauses to your query builder instance before calling chunkById(), as this will conflict with the keyset sorting logic.

Conclusion

When batch processing large datasets in Laravel, avoiding high MySQL OFFSET values is essential for scalability. By switching from chunk() to chunkById(), you eliminate query degradation, prevent accidental record skipping during updates, and ensure your batch commands complete in record time.

Thank you for reading this article 😊

For any query, do not hesitate to comment 💬