MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production

Why EXPLAIN Belongs in Your Daily Workflow

Most Laravel developers encounter slow queries in production, then scramble to fix them. The better habit is to run EXPLAIN during development on any query that touches a large table or joins multiple relations. MySQL's query planner will tell you exactly what it intends to do — and the output is far less cryptic than it first appears.

Reading EXPLAIN Output

The two columns that matter most are type and Extra.

type describes how MySQL accesses the table, ordered from worst to best:

| type | meaning | |---|---| | ALL | Full table scan — almost always wrong on large tables | | index | Full index scan — better, but still reads every leaf | | range | Index range scan — acceptable for bounded queries | | ref | Non-unique index lookup — good | | eq_ref | Unique index lookup per row — great for joins | | const | Single row via primary key — optimal |

Extra flags like Using filesort or Using temporary signal that MySQL had to sort or buffer rows outside the index, which is expensive at scale.

Running EXPLAIN from Laravel

You can grab the raw EXPLAIN rows directly from the query builder:

$sql = User::where('tenant_id', $tenantId)
    ->where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->toSql();

$bindings = User::where('tenant_id', $tenantId)
    ->where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->getBindings();

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

For a richer view, use EXPLAIN FORMAT=JSON — it exposes cost estimates and loop counts that the tabular format hides:

$plan = DB::select(
    'EXPLAIN FORMAT=JSON ' . $sql,
    $bindings
);
$decoded = json_decode($plan[0]->EXPLAIN, true);

Look for "cost_info" nodes with high "read_cost" values and "rows_examined_per_scan" counts that dwarf "rows_produced_per_join".

Wiring in the Slow Query Log

For staging environments, enable MySQL's slow query log to catch queries your test suite misses:

# 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 get aggregated statistics grouped by query fingerprint — far more useful than reading raw log lines.

Catching Issues in Development with Laravel Telescope and Debugbar

Both tools surface query counts and durations without leaving your browser:

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

This lightweight listener logs anything over 100 ms to your local log, giving you a searchable history without a UI dependency.

A Composite Index Pattern Worth Knowing

When you filter on tenant_id and status and sort by created_at, a single-column index on any one of those fields will not satisfy the full query. A composite index in the right column order will:

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

MySQL can use this index for the equality filters and the sort in one pass — Extra will show Using index condition instead of Using filesort.

Takeaways

• type: ALL in EXPLAIN is a red flag; const or eq_ref is the goal.
• EXPLAIN FORMAT=JSON gives cost estimates the tabular format omits.
• The slow query log with log_queries_not_using_indexes catches regressions in staging before production.
• A DB::listen hook in local environments gives you a zero-overhead early warning system.
• Composite index column order matters: equality columns first, range or sort column last.