MySQL EXPLAIN and Index Tuning for Laravel: Reading Query Plans in Production

Why EXPLAIN Still Matters in 2025

Laravel's Eloquent ORM is expressive, but it abstracts away the SQL layer just enough to let expensive queries slip into production unnoticed. Telescope and Debugbar will show you that a query is slow; EXPLAIN tells you why.

This article focuses on reading EXPLAIN output, recognising the patterns that hurt, and applying composite and covering indexes to fix them — all in a Laravel context.

Getting the Raw EXPLAIN Output

The quickest way to inspect a query from an Eloquent builder:

$query = Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->limit(50);

// Dump the raw SQL
ray($query->toRawSql());

// Or run EXPLAIN directly
$plan = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings());
dd($plan);

For EXPLAIN ANALYZE (MySQL 8.0+, returns tree-format output):

$plan = DB::select(
    'EXPLAIN ANALYZE ' . $query->toSql(),
    $query->getBindings()
);

Reading the Key Columns

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

A type: ALL with rows: 2000000 on an orders table is an immediate red flag, regardless of how clean the Eloquent code looks.

Composite Index Column Order

MySQL uses a B-tree index left-to-right. The rule: equality columns first, range column last, sort column last.

For this query:

SELECT * FROM orders
WHERE tenant_id = 1
  AND status = 'pending'
ORDER BY created_at ASC
LIMIT 50;

The optimal index is:

// Migration
$table->index(['tenant_id', 'status', 'created_at'], 'orders_tenant_status_created');

MySQL can satisfy the WHERE with equality lookups on the first two columns, then walk the B-tree in created_at order — eliminating the filesort entirely.

Covering Indexes to Avoid Heap Lookups

A covering index includes every column the query needs, so MySQL never touches the actual row data.

// Query only needs these columns
$orders = Order::select('id', 'status', 'total', 'created_at')
    ->where('tenant_id', $tenantId)
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->get();

// Covering index
$table->index(
    ['tenant_id', 'status', 'created_at', 'id', 'total'],
    'orders_covering'
);

When EXPLAIN shows Extra: Using index, the covering index is in play and heap I/O drops to zero.

Watch Out for SELECT *

SELECT * breaks covering indexes immediately. Scope your select() calls in read-heavy paths.

Diagnosing Filesorts on Paginated Queries

Laravel's paginate() runs a COUNT(*) subquery and a LIMIT/OFFSET query. Both must be fast.

// Slow: filesort + offset scan
$orders = Order::where('tenant_id', $id)
    ->orderBy('created_at')
    ->paginate(25);

If EXPLAIN shows Using filesort, the index doesn't cover the ORDER BY. Add created_at as the trailing column in your composite index.

For very large offsets, switch to cursor pagination:

$orders = Order::where('tenant_id', $id)
    ->orderBy('created_at')
    ->cursorPaginate(25);

Cursor pagination rewrites the WHERE clause to use a keyset (WHERE created_at > ?), which is index-friendly and avoids scanning discarded rows.

Practical Workflow

1. Enable the slow query log (long_query_time = 0.1) in staging.
2. Pull the worst offenders with pt-query-digest or the Performance Schema.
3. Run EXPLAIN ANALYZE on each query.
4. Add or adjust indexes in a migration; re-run EXPLAIN to confirm type improved.
5. Verify with SHOW STATUS LIKE 'Handler_read%' — Handler_read_rnd_next should drop.

Takeaways

• EXPLAIN type: ALL is always worth investigating before adding caching.
• Composite index column order follows: equality → range → sort.
• Covering indexes eliminate heap lookups; pair them with explicit select() calls.
• Using filesort in Extra usually means the trailing sort column is missing from the index.
• Cursor pagination is index-friendly; OFFSET pagination is not at scale.
• Measure with EXPLAIN ANALYZE after every index change — assumptions are often wrong.