Laravel Query Optimization: 4 Simple Fixes for Faster Apps

When a Laravel page or job feels slow, the database is the first suspect, and usually the right one. The good news is that you rarely need clever tricks. Most slowness comes from a few everyday habits: wrapping a column in a function so the database cannot use its index, doing arithmetic in PHP that the database does in milliseconds, writing rows one at a time, and loading a huge pile of rows into memory at once. I tested four of these on a table with one million orders (Laravel 13, MariaDB 10.11), so you can see how big the difference really is. (Selecting fewer columns, indexes and eager loading are covered in separate articles, so they are not repeated here.)

1. Do not wrap an indexed column in a function

This one catches many people because the code looks perfectly reasonable. whereDate() is convenient, but it wraps the column in a function, and the database can no longer use the index to jump to the right rows:

// Looks nice, scans the whole index
Order::whereDate('created_at', '2024-03-15')->count();
// select ... where date(`created_at`) = ?

// Same result, uses the index to jump to one day
Order::where('created_at', '>=', '2024-03-15 00:00:00')
     ->where('created_at', '<', '2024-03-16 00:00:00')
     ->count();

Both returned 2,880 orders, with an index on created_at in place. The whereDate version took about 172 ms and examined roughly 996,000 index entries. The range version took 1.3 ms and examined 2,880. The range also has a side benefit: it handles the end of the day correctly, with no confusion about midnight.

2. Let the database do the math

It is tempting to fetch rows and then total them up in PHP, because Laravel collections make it so easy: get()->sum('total_cents'). The catch is that get() builds a full model object for every row first, just so you can add up one number. The database can add the numbers itself and send back a single value.

// Builds 250,000 models, then adds them up in PHP
$total = Order::where('status', 'paid')->get()->sum('total_cents');

// The database does the addition, PHP gets one number
$total = Order::where('status', 'paid')->sum('total_cents');

Over 250,000 paid orders, both returned 12,500,250,000. The collection version took 5.8 seconds and peaked at 302 MB of memory. The database version took 0.15 seconds and 6 MB. The same applies to count(), avg(), min() and max().

Grouping is where it gets dramatic. Here is the total per status across all one million orders:

// Loads 1,000,000 rows, then groups them in PHP
$totals = Order::select('id', 'status', 'total_cents')->get()
    ->groupBy('status')
    ->map(fn ($rows) => $rows->sum('total_cents'));

// One query, four rows come back
$totals = Order::selectRaw('status, sum(total_cents) as total')
    ->groupBy('status')
    ->pluck('total', 'status');

The PHP version took about 25.8 seconds and peaked above 1.1 GB. The database version took 1.1 seconds and 6 MB. Rule of thumb: if you only want a number or a summary, ask for the number or the summary, not the rows. One small thing to watch: the database can return sums as strings (my pluck result came back as "12500250000"), so cast to int if you need a number.

3. Write in bulk, not one row at a time

Every create() or save() is its own trip to the database. A few is fine. Thousands in a loop add up fast, because each one pays the full round trip and commit cost. I inserted 5,000 rows three ways:

// 5,000 separate queries, each committed on its own
foreach ($rows as $row) {
    Order::create($row);
}

// Same 5,000 queries, but one commit at the end
DB::transaction(function () use ($rows) {
    foreach ($rows as $row) {
        Order::create($row);
    }
});

// 5 queries of 1,000 rows each
foreach (array_chunk($rows, 1000) as $batch) {
    Order::insert($batch);
}

Updates work the same way. I had 10,000 pending rows to mark as done. Looping over them and calling save() took 5.0 seconds and ran 10,001 queries (one to read, 10,000 to write). A single mass update did the same job in 0.06 seconds with one query:

// 10,001 queries
foreach (Work::all() as $work) {
    $work->status = 'done';
    $work->save();
}

// 1 query
Work::where('status', 'pending')->update(['status' => 'done']);

There is a trade-off, and it is worth knowing before you switch. Bulk insert() and mass update() skip model events, so observers and listeners do not fire, and insert() does not fill created_at and updated_at for you (I confirmed both when comparing Eloquent and the query builder). If your app depends on those, add the timestamps to your rows yourself or keep the slower path for those tables. For plain data like imports, counters and status flags, bulk is the better choice. For inserts that may already exist, look at upsert().

4. Process big tables in batches: chunkById, lazyById and cursor

Calling get() on a huge table loads every row as an object into memory at once. That is fine for hundreds of rows and dangerous for hundreds of thousands. For exports, cleanups and background jobs, Laravel gives you four ways to work through rows without holding them all at once. They are not equally good, so here is what I measured.

// 1. Batches of rows, you handle each batch
Order::where('id', '<=', 300000)->chunkById(1000, function ($orders) {
    foreach ($orders as $order) {
        // do the work
    }
});

// 2. One row at a time, loaded in batches behind the scenes
foreach (Order::where('id', '<=', 300000)->lazyById(1000) as $order) {
    // do the work
}

// 3. One query, one row at a time
foreach (Order::where('id', '<=', 300000)->cursor() as $order) {
    // do the work
}

// 4. lazy(): like lazyById, but pages with offsets (usually the wrong choice)
foreach (Order::where('id', '<=', 300000)->lazy(1000) as $order) { ... }

Each test below ran in a fresh PHP process. The first set walks 300,000 narrow order rows. The second walks 5,000 articles with a body of roughly 30 KB each:

What the numbers say

• chunkById()andlazyById()are the good defaults. They were the fastest and kept memory low. They work by asking for rows with an id greater than the last one seen, so each batch costs about the same no matter how deep you are. Pick chunkById() if you want to handle a batch at a time, and lazyById() if you prefer a simple foreach.
• cursor()is easy to misjudge. It runs a single query and gives you one model at a time, so it sounds like the lightest option. On narrow rows it used 28 MB, but on the 30 KB articles it peaked at 166 MB, compared with 38 MB for chunkById(). Memory grew with the size of each row, which is consistent with the database driver fetching the whole result into PHP before Laravel hands rows over one by one (I did not dig into the driver to confirm that). It was also slower than the batched methods on the 300,000-row run. Use it for narrow rows, and pair it with select() so each row stays small when your table is wide.
• lazy()is the odd one out. It looks like lazyById(), but it pages with offsets, so it re-skips more and more rows as it goes. It took 13.4 s against 3.4 s for lazyById(), and it has a worse problem, shown next.

The trap: changing the rows you are looping over

A very common job is "find everything pending, process it, mark it done". I tested exactly that on 10,000 pending rows, updating each row's status inside the loop:

lazy()      processed 5,000 of 10,000, 5,000 still pending
lazyById()  processed 10,000 of 10,000, 0 still pending
cursor()    processed 10,000 of 10,000, 0 still pending

With lazy(), every row that gets marked done disappears from the filter, so the next page's offset jumps over rows that were never processed. Half the work was silently skipped, with no error. The same offset-based behaviour applies to the plain chunk() method, so use the ById versions whenever your loop changes the column you filter on. cursor() was safe here because the whole result was already fetched when the loop started.

Is this an optimization for web pages?

Not really. These methods save memory for batch jobs: exports, imports, cleanups, queue jobs and artisan commands. For a page or API response, show a limited number of rows with pagination instead, and do not loop over a whole table inside a web request.

How to find the slow queries in the first place

Guessing is slow. Three tools you already have:

• toSql() shows the SQL a query builder will run, so you can see what you actually asked for.
• explain() asks the database how it plans to run the query. Look for type: ALL and very large rows numbers.
• DB::listen() lets you log every query with its bindings and time. In my test, one callback printed select count(*) as aggregate from orders where date(created_at) = ? | ["2024-03-15"] | 252.84 ms for the slow query above.

Quick checklist

• Compare dates with a range, not with a function on the column.
• Ask the database for totals and groups instead of loading rows to add them up.
• Insert and update in bulk, and remember that bulk skips model events.
• Use chunkById() or lazyById() for batch jobs over more than a few thousand rows, and avoid lazy() and chunk() when you update the rows you filter on.

Frequently asked questions

Is it safe to skip model events with bulk writes?

It depends on your app. If you use observers for audit logs, search indexing or cache clearing, bulk writes will skip them. In that case, trigger the same work yourself after the bulk operation, or keep the per-row path for that model.

How big should an insert batch be?

I used 1,000 rows per query, which worked well. Very large batches can hit the database's maximum packet size or placeholder limits, so start around 500 to 1,000 and measure.

Is Eloquent the reason my queries are slow?

Usually not. Eloquent adds some overhead for building model objects, which is why large result sets use more memory, but most slow pages are caused by the query itself or by doing in PHP what the database should do.

Will caching fix a slow query?

It can hide one, but the query stays slow every time the cache expires. Fix the query first, then cache what is still expensive.

Originally published on DEV Talk.