MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning

Why Guessing Doesn't Scale

Most Laravel performance problems live in the database, not in PHP. Yet the default debugging loop—add a dd(), stare at Telescope, shrug—rarely surfaces why a query is slow. This article gives you a repeatable workflow: capture slow queries, read execution plans, and apply the right index.

Step 1: Capture Queries Worth Investigating

Before touching EXPLAIN, know which queries deserve attention. Laravel's query listener is the fastest way to log anything over a threshold during development.

// AppServiceProvider::boot()
DB::listen(function (QueryExecuted $event) {
    if ($event->time > 100) { // milliseconds
        logger()->warning('Slow query', [
            'sql' => $event->sql,
            'bindings' => $event->bindings,
            'time_ms' => $event->time,
            'connection' => $event->connectionName,
        ]);
    }
});

In production, enable MySQL's own slow query log instead:

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Parse the log with pt-query-digest (Percona Toolkit) to rank queries by total time, not just worst single execution.

Step 2: Run EXPLAIN ANALYZE

MySQL 8.0+ supports EXPLAIN ANALYZE, which actually executes the query and reports real row counts alongside estimates. The gap between estimated and actual rows is your first signal.

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

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

Key fields to read:

• type: ALL means full table scan — almost always wrong on large tables.
• key: which index MySQL chose (or NULL if none).
• rows: estimated rows examined. Multiply across joins for the real cost.
• Extra: watch for Using filesort and Using temporary — both indicate missing or wrong indexes.

Reading a Real Plan

-> Sort: users.created_at  (actual rows=18340, loops=1)
    -> Filter: (users.status = 'active')  (actual rows=18340)
        -> Index lookup on users using idx_tenant (tenant_id=42)
           (actual rows=21050, loops=1)

MySQL used idx_tenant to narrow to 21 k rows, then filtered and sorted in memory. The sort on created_at is the bottleneck.

Step 3: Apply a Covering Index

A covering index includes every column the query touches, so MySQL never visits the clustered index (the actual table rows).

// Migration
Schema::table('users', function (Blueprint $table) {
    // Composite: filter columns first, then the ORDER BY column
    $table->index(['tenant_id', 'status', 'created_at'], 'idx_users_tenant_status_created');
});

After adding the index, re-run EXPLAIN ANALYZE:

-> Index range scan on users using idx_users_tenant_status_created
   (actual rows=18340, loops=1)

Using filesort is gone. MySQL walks the index in order and returns rows directly — no sort step, no secondary lookup.

Step 4: Validate in Staging, Not Just Dev

Index effectiveness depends on data distribution. A column with two distinct values (status = active|inactive) may not benefit from an index if 90 % of rows are active. Use SHOW INDEX FROM users and check Cardinality.

For low-cardinality columns, a partial index (MySQL calls it a filtered index via a generated column or a prefix) or reordering the composite index can help. Always compare EXPLAIN ANALYZE output before and after on a dataset that mirrors production row counts.

Takeaways

• Use DB::listen locally and MySQL's slow query log in production to find real offenders.
• EXPLAIN ANALYZE (MySQL 8.0+) shows actual row counts — trust it over estimates.
• Using filesort and Using temporary in Extra are immediate action items.
• Design composite indexes with equality columns first, then the ORDER BY column.
• A covering index eliminates secondary row lookups and is often the single highest-impact change.
• Validate index cardinality with production-scale data before shipping.