MySQL EXPLAIN and Index Optimization for Laravel Developers

Why EXPLAIN Is Your First Tool, Not Your Last

Most Laravel developers reach for DB::listen() to log slow queries, then add an index and hope for the best. That workflow skips the most important step: understanding why MySQL chose the execution plan it did. EXPLAIN tells you exactly that.

// Quick EXPLAIN wrapper you can drop in a tinker session
$sql = User::where('tenant_id', 1)
    ->where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->toSql();

$bindings = [1, 'active'];

$plan = DB::select('EXPLAIN ' . $sql, $bindings);
dd($plan);

The columns that matter most are type, key, rows, and Extra.

| type value | What it means | |---|---| | ALL | Full table scan — almost always bad | | index | Full index scan — better, still expensive | | range | Index range scan — usually acceptable | | ref | Non-unique index lookup — good | | eq_ref | Unique index lookup — best for joins | | const | Single-row lookup via primary key — ideal |

If you see type: ALL on a table with more than a few thousand rows, you have a problem.

Reading a Real Slow Query

Consider a multi-tenant SaaS where orders are filtered by tenant, date range, and status:

Order::where('tenant_id', $tenantId)
    ->whereBetween('created_at', [$start, $end])
    ->where('status', 'pending')
    ->orderBy('created_at', 'desc')
    ->get();

With only a single index on tenant_id, EXPLAIN might show:

type: ref | key: idx_tenant_id | rows: 84000 | Extra: Using where; Using filesort

MySQL found the tenant's rows via the index but then scanned all 84,000 of them in memory to apply the date and status filters, then sorted the result. The Using filesort in Extra is the giveaway.

Designing the Right Composite Index

The rule of thumb for composite indexes: equality columns first, range column last, sort column matches range column.

CREATE INDEX idx_orders_tenant_status_created
    ON orders (tenant_id, status, created_at);

Now MySQL can seek directly to (tenant_id=X, status='pending') and walk the created_at range in index order — no filesort needed.

Verify it in a migration:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['tenant_id', 'status', 'created_at'], 'idx_orders_tenant_status_created');
});

After the migration, re-run EXPLAIN and confirm type is now range and Extra no longer shows Using filesort.

Covering Indexes: Eliminating Table Row Lookups

If your query only selects a handful of columns, a covering index lets MySQL answer the query entirely from the index without touching the actual table rows.

Order::where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->select(['id', 'total', 'created_at'])
    ->orderBy('created_at', 'desc')
    ->get();

CREATE INDEX idx_orders_covering
    ON orders (tenant_id, status, created_at, id, total);

EXPLAIN will show Extra: Using index — the entire result came from the index structure. This can cut I/O dramatically on large tables.

Forcing and Hinting Indexes When the Optimizer Gets It Wrong

MySQL's optimizer occasionally picks the wrong index, especially when statistics are stale. You can hint or force a specific index:

// Hint (optimizer may still ignore it)
DB::table('orders')->from(DB::raw('orders USE INDEX (idx_orders_tenant_status_created)'))
    ->where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->get();

// Force (optimizer must use it)
DB::table('orders')->from(DB::raw('orders FORCE INDEX (idx_orders_tenant_status_created)'))
    ->where('tenant_id', $tenantId)
    ->get();

Use ANALYZE TABLE orders; first to refresh statistics before resorting to hints.

Integrating EXPLAIN Into Your Development Workflow

Add a dev-only service provider that logs slow query plans automatically:

if (app()->isLocal()) {
    DB::listen(function (QueryExecuted $event) {
        if ($event->time > 100) { // ms
            $plan = DB::select('EXPLAIN ' . $event->sql, $event->bindings);
            logger()->warning('Slow query plan', [
                'sql'  => $event->sql,
                'time' => $event->time,
                'plan' => $plan,
            ]);
        }
    });
}

Pair this with Telescope's query watcher in staging and you'll catch regressions before they reach production.

Takeaways

• EXPLAIN type: ALL is a red flag; const and eq_ref are your targets.
• Composite index column order matters: equality → range → sort.
• Covering indexes eliminate row lookups and show Using index in Extra.
• Using filesort means MySQL is sorting in memory or on disk — fix it with index ordering.
• Stale statistics mislead the optimizer; run ANALYZE TABLE before blaming the index.
• Automate slow-query EXPLAIN logging in local and staging environments.