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

The Problem With Guessing

Most Laravel performance issues are diagnosed by intuition: add an index, wrap something in with(), call it done. That works until it doesn't. When a query regresses under real data volumes, you need instrumentation — not hunches.

This article walks through a disciplined workflow: capture slow queries, read execution plans, and apply surgical fixes including index hints when the optimizer makes the wrong call.

Step 1: Capture Slow Queries in Laravel

Before touching MySQL directly, wire up a listener in a service provider so you always know which Eloquent call produced the SQL:

// app/Providers/AppServiceProvider.php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

public function boot(): void
{
    if (config('app.debug')) {
        DB::listen(function ($query) {
            if ($query->time > 100) { // ms
                Log::channel('slow_queries')->warning('Slow query', [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time_ms' => $query->time,
                ]);
            }
        });
    }
}

In production, prefer MySQL's own slow query log (long_query_time = 0.1, log_queries_not_using_indexes = ON) and ship those logs to your observability stack. The Laravel listener is great for development; the server log catches queries that bypass PHP entirely (background jobs, direct connections).

Step 2: Read EXPLAIN ANALYZE, Not Just EXPLAIN

EXPLAIN shows the optimizer's plan. EXPLAIN ANALYZE actually runs the query and shows measured row counts and timings. The gap between estimated and actual rows is where bugs hide.

EXPLAIN ANALYZE
SELECT orders.*, customers.name
FROM orders
INNER JOIN customers ON customers.id = orders.customer_id
WHERE orders.status = 'pending'
  AND orders.created_at > NOW() - INTERVAL 7 DAY
ORDER BY orders.created_at DESC
LIMIT 50;

Key things to look for in the output:

• type: ALL — full table scan, almost always wrong on large tables.
• rows estimate vs actual — a 10× gap means stale statistics; run ANALYZE TABLE orders.
• Using filesort — the ORDER BY cannot use an index; consider a composite index that covers both the WHERE predicates and the ORDER BY column.
• Using temporary — a temp table was created, often from a GROUP BY or DISTINCT that doesn't align with an index.

Step 3: Composite Index Design for the Query Above

The query filters on status and created_at, then sorts on created_at. A single-column index on either field is suboptimal. The right composite index puts the equality predicate first:

ALTER TABLE orders
  ADD INDEX idx_orders_status_created (status, created_at DESC);

In a Laravel migration:

$table->index(['status', 'created_at'], 'idx_orders_status_created');

After adding the index, re-run EXPLAIN ANALYZE and confirm type changes to range or ref and Extra no longer shows Using filesort.

Step 4: Force an Index When the Optimizer Gets It Wrong

MySQL's cost-based optimizer occasionally picks a worse index because its statistics are stale or the data distribution is unusual. Laravel's query builder exposes raw FROM clauses, but the cleanest escape hatch is a raw expression:

$orders = DB::table(DB::raw('orders USE INDEX (idx_orders_status_created)'))
    ->join('customers', 'customers.id', '=', 'orders.customer_id')
    ->where('orders.status', 'pending')
    ->where('orders.created_at', '>', now()->subDays(7))
    ->orderByDesc('orders.created_at')
    ->limit(50)
    ->get();

Use USE INDEX to suggest, FORCE INDEX to mandate. Prefer USE INDEX — it still allows a full scan if the index would be worse, which is a safety net. Reserve FORCE INDEX for cases where you have profiled and are certain.

Step 5: Keep Statistics Fresh

Index hints are a last resort. The first resort is accurate statistics:

ANALYZE TABLE orders;
-- or for InnoDB, update the sample pages:
SET GLOBAL innodb_stats_persistent_sample_pages = 64;

Schedule ANALYZE TABLE on high-churn tables during low-traffic windows. Stale statistics cause the optimizer to underestimate row counts and choose index scans over full scans (or vice versa).

Takeaways

• Use DB::listen for development visibility; rely on MySQL's slow query log in production.
• EXPLAIN ANALYZE gives measured timings — always prefer it over plain EXPLAIN.
• Design composite indexes with equality predicates first, range/sort columns last.
• USE INDEX is a hint; FORCE INDEX is a mandate — reach for the hint first.
• Stale statistics cause optimizer mistakes; ANALYZE TABLE is cheap and often fixes phantom slowdowns.