MySQL Optimization for Laravel: Covering Indexes, EXPLAIN ANALYZE, and Query Profiling

Why Your Eloquent Queries Are Slower Than They Should Be

Most Laravel performance problems trace back to one of three root causes: missing indexes, indexes that exist but are never used, or queries that fetch far more data than they need. The fix is rarely "add Redis" — it's understanding what MySQL is actually doing.

This article focuses on three concrete skills: reading EXPLAIN ANALYZE output, building covering indexes, and profiling slow queries in a Laravel context.

Reading EXPLAIN ANALYZE

MySQL 8.0+ supports EXPLAIN ANALYZE, which executes the query and returns real timing data alongside the estimated plan. Run it directly or via Laravel's query log:

// Log the raw SQL, then run EXPLAIN ANALYZE in your DB client
$sql = User::where('status', 'active')
    ->where('created_at', '>=', now()->subDays(30))
    ->orderBy('created_at')
    ->toSql();

// Or use DB::select directly
$plan = DB::select('EXPLAIN ANALYZE ' . $sql, ['active', now()->subDays(30)]);

Key fields to watch:

• type: ALL means a full table scan. ref or range means an index is being used.
• rows: MySQL's estimate of rows examined. A high number relative to returned rows signals a poor index.
• Extra: Using filesort and Using temporary are red flags for ORDER BY and GROUP BY performance.
• actual time: In EXPLAIN ANALYZE, the actual time=X..Y values show real loop timing in milliseconds.

-> Index range scan on users using idx_status_created  (cost=120.5 rows=980)
   (actual time=0.412..3.201 rows=874 loops=1)

If rows is 50,000 but actual rows is 12, your index is working but the selectivity is poor — consider a more selective composite index.

Covering Indexes: The Single Biggest Win

A covering index includes every column the query needs, so MySQL never touches the actual table rows (no "heap fetch"). This is especially powerful for paginated list queries.

Consider a typical admin list:

User::where('status', 'active')
    ->select('id', 'name', 'email', 'created_at')
    ->orderBy('created_at', 'desc')
    ->paginate(25);

A standard index on status forces MySQL to fetch the row for every match to retrieve name, email, and created_at. A covering index eliminates that:

// In a migration
Schema::table('users', function (Blueprint $table) {
    $table->index(['status', 'created_at', 'name', 'email'], 'idx_users_covering_list');
});

Now EXPLAIN will show Using index in the Extra column — the query is satisfied entirely from the index B-tree.

Rule of thumb: put the equality columns first (status), then the range/sort column (created_at), then the projected columns.

Profiling Slow Queries in Laravel

Enable the slow query log in MySQL (long_query_time = 1) and point mysqldumpslow at the log. For development, Laravel's built-in query listener is faster:

// AppServiceProvider::boot()
if (app()->isLocal()) {
    DB::listen(function ($query) {
        if ($query->time > 100) { // ms
            logger()->warning('Slow query', [
                'sql'  => $query->sql,
                'time' => $query->time,
                'bindings' => $query->bindings,
            ]);
        }
    });
}

For production, use Laravel Telescope's query watcher or Pulse's slow query recorder — both surface the call stack so you can trace the Eloquent call site without grep.

Practical Checklist

• Always run EXPLAIN ANALYZE before shipping a new query that touches large tables.
• Composite index column order matters: equality predicates first, range/sort last, projected columns after.
• Covering indexes eliminate heap fetches and are the highest-leverage optimization for read-heavy list endpoints.
• Using filesort is not always fatal — if the result set is small, MySQL sorts in memory quickly. It becomes a problem at scale.
• DB::listen in local/staging catches slow queries before they reach production.
• Avoid SELECT * in Eloquent — it prevents covering indexes from working and increases network payload.

Takeaways

• EXPLAIN ANALYZE gives you real execution timing, not just estimates — use it on every non-trivial query.
• Covering indexes are the single most impactful optimization for paginated list queries in Laravel admin panels.
• Laravel's DB::listen and Telescope's query watcher are your first-line profiling tools before reaching for external APMs.
• Column order in composite indexes is not arbitrary — get it wrong and MySQL ignores the index entirely.