Killing the N+1 Problem in Laravel: From 120 Queries to 8 in 20 Minutes

TL;DR — The N+1 query problem silently destroys performance on list pages.
Fix it with targeted eager loading (with()), SQL-side aggregates
(withCount, withSum), and basic indexing. Measure with Debugbar/Telescope
or EXPLAIN ANALYZE. This post shows you exactly how.

The Symptom: 1 + N Queries

You render a list (orders, posts, products…). For each row, you touch a related model (->customer, ->items, ->author), and Eloquent fires one extra query per row.

That’s 1 query to fetch the list + N queries for each row’s relation:
on 20 rows, you’re often at 120 queries once you add counts and nested relations.

Before: classic N+1

// Controller / Repository
$orders = Order::latest()->take(20)->get();

foreach ($orders as $o) {
    echo $o->customer->name;   // extra query per row
    echo $o->items()->count(); // extra query per row
}

This “works”, but it’s expensive and gets worse with volume.

The Fix: Load Once, Use Many

After: eager loading + SQL aggregates

$orders = Order::with(['customer:id,name']) // bring the relation in one shot
               ->withCount('items')         // compute count in SQL
               ->latest()
               ->take(20)
               ->get();

foreach ($orders as $o) {
    echo $o->customer->name;  // already in memory
    echo $o->items_count;     // precomputed by the DB
}

Results: in typical setups, 8–10 queries total instead of 100+.

---

How to Detect N+1 Quickly

1. Laravel Debugbar or Telescope: look at total query count on the page.
2. Enable strict mode in local only to reveal lazy loads:

// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

public function boot()
{
    if (!app()->isProduction()) {
        Model::preventLazyLoading();
    }
}

This will throw in dev when a lazy load occurs, pointing you to the exact line.

---

Eager Loading, Properly

1) Target only what you need

Load relations and restrict columns to keep payloads small:

$posts = Post::with([
    'author:id,name',
    'tags:id,name',
])->get(['id','title','author_id']);

2) Go deep if required

$posts = Post::with(['comments.author'])->get();

3) On existing collections

Already have models loaded? Add relations without refetching everything:

$orders->load(['customer', 'items']);
$orders->loadCount('items');

4) Avoid duplicate eager loads

$orders->loadMissing('customer');

---

Aggregates You Should Use (All in SQL)

• withCount('items')
• withSum('items as total_price', 'price')
• withAvg('items', 'price')

Example:

$orders = Order::with('customer:id,name')
    ->withCount('items')
    ->withSum('items as total_price', 'price')
    ->latest()
    ->take(20)
    ->get();

Now your Blade reads cleanly:

{{ $order->customer->name }}
{{ $order->items_count }}
{{ number_format($order->total_price, 2) }}

---

Database Side: Give the Planner a Chance

Even perfect eager loading can suffer if your DB lacks indexes.

Index your foreign keys

PostgreSQL

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_items_order_id ON items(order_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);

MySQL

ALTER TABLE items   ADD INDEX idx_items_order_id (order_id);
ALTER TABLE orders  ADD INDEX idx_orders_customer_id (customer_id);

Measure with EXPLAIN ANALYZE

Check that the planner uses sensible operations (Index Scan, Hash Aggregate).
If you see full scans on big tables, you probably need indexes or better filters.

---

Real-World Checklist (copy/paste)

• [ ] Use with() on every list that touches relations
• [ ] Replace per-row ->count() with withCount()
• [ ] Replace per-row sums with withSum()
• [ ] Restrict columns: relation:id,needed_columns
• [ ] Add indexes on FK columns
• [ ] Enable Model::preventLazyLoading() locally
• [ ] Verify query count with Debugbar/Telescope
• [ ] Sanity-check plans with EXPLAIN ANALYZE

---

Bonus: When You Still Need Lazy Loading

Sometimes you genuinely need ad-hoc relation access. Keep it explicit and rare,
or fetch small data where it doesn’t hurt.

Order::whereKey($id)->first()->load('customer');

Tip: pair it with caching (Cache::remember()) if the data is reused often.

---

Minimal “Before/After” You Can Share as a Visual

Use this single-screen diff for your team or social posts:

- // BEFORE — N+1: 1 list query + N per-row relations
- $orders = Order::latest()->take(20)->get();
- foreach ($orders as $o) {
-   echo $o->customer->name;   // query per row
-   echo $o->items()->count(); // query per row
- }

+ // AFTER — eager + SQL aggregates: ~8 total queries
+ $orders = Order::with(['customer:id,name'])
+                ->withCount('items')
+                ->latest()->take(20)->get();
+ foreach ($orders as $o) {
+   echo $o->customer->name;   // already loaded
+   echo $o->items_count;      // computed in SQL
+ }

---

Common Pitfalls & How to Avoid Them

• Loading too much: with('bigRelation') without column limits can bloat memory. Use with(['bigRelation' => fn ($q) => $q->select('id','fk_id','name')]).
• Nested N+1: Fix the first relation… and forget the second (comments.author). Load both.
• Counting via collection: Avoid ->items->count() (collection) when a withCount('items') exists.
• Cache invalidation: If you cache, always key on filters (user, shop, locale…).

---

Why This Matters (Beyond Benchmarks)

• Faster pages → higher conversion (call, book, buy).
• Lower DB pressure → smaller instances, better stability.
• Cleaner code → easier to evolve without regressions.