MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries

Why Guessing Doesn't Scale

Most Laravel developers reach for an index when a query feels slow, add one, and hope for the best. That workflow is fragile. MySQL's EXPLAIN statement tells you exactly what the optimizer decided to do — which index it chose, how many rows it expects to examine, and where it gave up and scanned the whole table. Reading it fluently is a force-multiplier skill.

Getting EXPLAIN Output Inside Laravel

You don't need a separate MySQL client. Wrap any Eloquent query with a quick macro or just use DB::select:

// Quick one-off during local debugging
$sql = User::where('tenant_id', 42)
    ->where('status', 'active')
    ->orderBy('created_at')
    ->toRawSql(); // Laravel 10.15+

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

For EXPLAIN ANALYZE (MySQL 8.0.18+, returns actual row counts and timing):

$plan = DB::select(
    'EXPLAIN ANALYZE SELECT * FROM users WHERE tenant_id = ? AND status = ? ORDER BY created_at',
    [42, 'active']
);

EXPLAIN ANALYZE runs the query for real, so use it on a staging replica, not production under load.

The Columns That Actually Matter

| Column | What to watch for | |---|---| | type | ALL = full scan (bad). Aim for ref, range, or eq_ref. | | key | NULL means no index was used. | | rows | Estimated rows examined — multiply across joined tables. | | Extra | Using filesort or Using temporary signals expensive post-processing. |

A type: ALL with rows: 800000 on a joined table is the single most actionable red flag you will encounter.

A Real-World Example: The Composite Index Fix

Consider this Eloquent scope that powers a Filament table:

Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->paginate(25);

EXPLAIN shows type: ref on a single-column tenant_id index, but Extra: Using filesort because created_at isn't in the index. MySQL fetches potentially thousands of rows, then sorts them in a temporary buffer.

The fix is a composite index that covers the filter and the sort:

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

After adding this index, EXPLAIN shows type: range, key: orders_tenant_status_created_idx, and Extra no longer contains Using filesort. The optimizer can satisfy the entire query — filter and sort — by walking the index in order.

When a Covering Index Goes Further

If your query only selects a handful of columns, you can make the index covering — MySQL never touches the table rows at all (Extra: Using index):

$table->index(
    ['tenant_id', 'status', 'created_at', 'id', 'total_cents'],
    'orders_covering_idx'
);

Then in Eloquent:

Order::select(['id', 'status', 'created_at', 'total_cents'])
    ->where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->paginate(25);

EXPLAIN now shows Extra: Using index. Zero heap reads.

Automating Detection in CI

Add a Pest test that asserts no full-table scans on your critical queries:

it('uses an index for the pending orders query', function () {
    $plan = DB::select(
        'EXPLAIN SELECT id, status, created_at FROM orders WHERE tenant_id = 1 AND status = "pending" ORDER BY created_at'
    );

    $types = collect($plan)->pluck('type');
    expect($types)->not->toContain('ALL');
});

This won't catch every regression, but it will catch the worst ones before they reach production.

Takeaways

• type: ALL with a high rows estimate is your highest-priority fix.
• Composite indexes must match the column order: equality filters first, then range/sort columns.
• EXPLAIN ANALYZE gives actual timing — use it on a replica.
• Covering indexes eliminate heap reads entirely; use select() to keep them narrow.
• A simple Pest assertion on EXPLAIN output can prevent index regressions in CI.