MySQL Query Optimization in Laravel: What I Fixed in Production

The Dashboard That Took 8 Seconds to Load

When I joined Smart Provider LLC, one of the first things I looked at was the SP360 dashboard load time. Eight seconds. For a page users open every morning.

Laravel Telescope showed the problem immediately: 400+ database queries on a single page request.

N+1. Classic. Entirely fixable.

Killing the N+1

N+1 happens when you loop over a collection and fire a new query for each item. Eloquent makes it easy to write this without realizing.

// Bad — 1 query for orders + 1 per order for the client
$orders = WorkOrder::all();
foreach ($orders as $order) {
    echo $order->client->name; // new query each iteration
}

// Good — 2 queries total regardless of collection size
$orders = WorkOrder::with('client')->get();

For nested relationships:

WorkOrder::with(['client', 'client.address', 'assignedTech', 'notes'])->paginate(25);

After fixing eager loading across the dashboard, load time dropped under one second. That's the highest-ROI database optimization in most Laravel apps — before you touch indexes, before you add caching.

Indexes That Actually Help

An index doesn't help every query. It helps queries that filter, sort, or join on a column with high selectivity. Adding indexes blindly makes writes slower without improving reads.

Columns worth indexing in a typical SaaS:

• Foreign keys (client_id, user_id) — older Laravel migrations don't always add these
• Status columns you frequently filter by
• created_at when you sort by date on large tables

// Migration — adding what should have been there from the start
$table->index('client_id');
$table->index(['status', 'created_at']); // composite for filter + sort together

Always check what MySQL is actually doing before adding an index:

EXPLAIN SELECT * FROM work_orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 25;

type: ALL on a large table means full scan — you need an index. type: range or type: ref means it's using one.

Eloquent Gotchas That Cost Performance

count() on a loaded collection vs the query builder:

// Loads every row into memory just to count
$count = WorkOrder::all()->count();

// SELECT COUNT(*) — what you actually want
$count = WorkOrder::count();

Select only the columns you need:

// SELECT * including large text columns you never use
$orders = WorkOrder::all();

// Lean query — only fetch what the page displays
$orders = WorkOrder::select('id', 'title', 'status', 'created_at')->paginate(25);

whereHas vs join for filtering at scale:

// Readable but generates a subquery — slow on large tables
WorkOrder::whereHas('client', fn ($q) => $q->where('region', 'northeast'))->get();

// Join — faster when filtering across large datasets
WorkOrder::join('clients', 'clients.id', '=', 'work_orders.client_id')
    ->where('clients.region', 'northeast')
    ->select('work_orders.*')
    ->get();

When to Drop to Raw SQL

Eloquent is the right tool for most queries. Reporting queries with multiple aggregations, window functions, or recursive CTEs are often cleaner and faster as raw SQL. Don't be precious about it.

$report = DB::select(<<<SQL
    SELECT
        c.name                                                         AS client_name,
        COUNT(wo.id)                                                   AS total_orders,
        SUM(CASE WHEN wo.status = 'completed' THEN 1 ELSE 0 END)      AS completed,
        AVG(TIMESTAMPDIFF(HOUR, wo.created_at, wo.completed_at))       AS avg_hours
    FROM clients c
    LEFT JOIN work_orders wo ON wo.client_id = c.id
    WHERE wo.created_at >= ?
    GROUP BY c.id, c.name
    ORDER BY total_orders DESC
SQL, [now()->subDays(30)]);

The Order That Works

Fix N+1 first. Add indexes next. Restructure queries last. Cache only after all three.

Caching a slow query makes it fast. Fixing the underlying query makes it unnecessary to cache at all — which is always the better outcome.