Optimizing MySQL for Laravel Apps: Indexes First

My order of operations

Find slow queries (slow query log, Telescope, EXPLAIN)
Add the right indexes
Kill N+1 Eloquent usage
Only then touch buffer sizes and server config

People love config tuning because it feels powerful. Indexes and query shape usually move the needle more on typical Laravel apps.

Find the slow query

DB::listen(function ($query) {
    if ($query->time > 100) {
        logger()->warning('slow', [
            'sql' => $query->sql,
            'time' => $query->time,
        ]);
    }
});

Or enable MySQL’s slow query log in non-prod first. Then run EXPLAIN / EXPLAIN ANALYZE (MySQL 8.0.18+) on the offender.

Indexes that matter

// Migration
$table->foreignId('user_id')->constrained()->index();
$table->string('status');
$table->index(['user_id', 'status']);

Composite indexes should match filter order. A lone index on status may not help WHERE user_id = ? AND status = ? as much as (user_id, status).

Eloquent habits

Eager load: with(['items', 'customer'])
Paginate: ->paginate(50) instead of unbounded get()
Select only needed columns on wide tables when it helps
Avoid whereHas heavy loops when a join or denormalized counter works
Be careful with chunk/lazy in long console jobs

Example: fix a classic report query

Before: load all orders, loop line items in PHP. After: aggregate in SQL or use a constrained query with indexes on orders.created_at and order_items.order_id.

$totals = Order::query()
    ->whereBetween('created_at', [$from, $to])
    ->selectRaw('status, COUNT(*) as c')
    ->groupBy('status')
    ->get();

When to tune the server

After queries are sane: buffer pool size, connections, and I/O settings for your host. If a report crushes production, move it to a replica or a queued job—not “hope nobody clicks export at noon.”

Official references: MySQL optimization, Laravel query builder.

Troubleshooting and common mistakes

Most failures I see are configuration and process issues, not “the framework is broken.” Slow down: reproduce on a clean environment, read the exact error, and change one variable at a time.

Confirm you are on the documented major version of the tool you are following.
Prefer official docs over random outdated blog snippets when commands disagree.
Keep lockfiles committed so teammates and CI install the same dependency graph.
Separate “works on my machine” fixes (PATH, SDK licenses, local services) from application bugs.

What to do next

Implement the smallest vertical slice from this article on a throwaway branch, then promote the patterns into your real app. Guides that stay theoretical never catch the auth, env, and deploy footguns that actually burn time.

Covering indexes (when they help)

If a query only needs a few columns and you hit it constantly, a covering index can avoid table lookups. Do not invent covering indexes for every report—measure first.

Transactions and locks

Long transactions hold locks. Keep HTTP requests short; move slow work to queues. Deadlocks often appear when two code paths update the same rows in different orders—make update order consistent.

Schema hygiene

Prefer sensible column types (unsignedBigInteger ids, proper string lengths)
Avoid nullable foreign keys “just in case” without a product reason
Archive or partition huge historical tables when reports scan years of rows