How to Find and Fix Slow MySQL Queries with EXPLAIN

-- Enable slow query log (add to my.cnf or run at runtime)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- log queries over 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- View the log
-- tail -f /var/log/mysql/slow.log

-- Or use mysqldumpslow to summarize it
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

<?php

// 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) { // only log queries over 100ms
                Log::channel('daily')->warning('Slow query', [
                    'sql'      => $query->sql,
                    'bindings' => $query->bindings,
                    'time_ms'  => $query->time,
                ]);
            }
        });
    }
}

EXPLAIN SELECT
    o.id,
    o.created_at,
    u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

-- Example bad output:
-- +----+------+--------+------+------+------+-------------+
-- | id | type | key    | ref  | rows | filt | Extra       |
-- +----+------+--------+------+------+------+-------------+
-- |  1 | ALL  | NULL   | NULL | 980k | 0.50 | Using where |
-- +----+------+--------+------+------+------+-------------+
-- type=ALL + key=NULL = full table scan on 980,000 rows

-- Add a composite index that covers the WHERE and ORDER BY
ALTER TABLE orders
    ADD INDEX idx_orders_status_created (status, created_at);

-- Run EXPLAIN again to verify
EXPLAIN SELECT o.id, o.created_at, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

-- Expected improved output:
-- +----+------+---------------------------+-------+------+
-- | id | type | key                       | rows  | Extra|
-- +----+------+---------------------------+-------+------+
-- |  1 | ref  | idx_orders_status_created |  4200 |      |
-- +----+------+---------------------------+-------+------+

-- Bad: function wraps the indexed column → full scan
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-15';

-- Bad: same problem with YEAR/MONTH
SELECT * FROM orders
WHERE YEAR(created_at) = 2024 AND MONTH(created_at) = 1;

-- Good: range query — MySQL can use the index
SELECT * FROM orders
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at <  '2024-01-16 00:00:00';

-- Good: LOWER() on a column kills the index too
-- Bad:  WHERE LOWER(email) = 'user@example.com'
-- Good: store emails already lowercased, or use a case-insensitive collation

-- MySQL 8.0+ only — actually runs the query
EXPLAIN ANALYZE
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

-- Output shows:
-- -> Limit: 20 row(s)  (actual time=0.318..0.319 rows=20 loops=1)
--   -> Index lookup on o using idx_orders_status_created
--      (status='pending')  (cost=1820 rows=4200)
--      (actual time=0.089..0.304 rows=20 loops=1)

<?php

// Bad: N+1 — fires one query per post
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // SELECT * FROM users WHERE id = ? × N
}

// Good: eager load — 2 queries total regardless of N
$posts = Post::with('author')->get();
foreach ($posts as $post) {
    echo $post->author->name;
}

// Also good: nested eager loading
$posts = Post::with(['author', 'comments.author'])->paginate(20);

// Laravel Debugbar or Telescope will flag N+1s visually —
// enable them in development and look for repeated identical queries.

-- Benchmark: compare before and after
-- 1. Record baseline
SET @start = NOW(6);
SELECT COUNT(*) FROM orders WHERE status = 'pending' ORDER BY created_at DESC;
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) / 1000 AS ms;

-- 2. Add the index
ALTER TABLE orders ADD INDEX idx_orders_status_created (status, created_at);

-- 3. Run again and compare
SET @start = NOW(6);
SELECT COUNT(*) FROM orders WHERE status = 'pending' ORDER BY created_at DESC;
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) / 1000 AS ms;

-- Also check SHOW STATUS LIKE 'Handler_read%' before and after
-- to see how many rows were actually read by the storage engine.