Query Profiling and EXPLAIN

Learning Focus

Use this lesson to master EXPLAIN and query profiling — the essential tools for understanding why a query is slow and how to make it fast.

Concept Overview

Why Profile Queries?

Slow queries cause:

• Poor user experience — pages take seconds to load
• Server overload — one bad query can consume all CPU and I/O
• Cascading failures — slow queries hold locks, causing other queries to wait

Query profiling reveals exactly what MySQL does when executing a query — which indexes it uses, how many rows it examines, and where time is spent.

The Profiling Toolkit

Tool	What It Shows
EXPLAIN	Query execution plan — indexes used, rows estimated, join type
EXPLAIN ANALYZE	Same + actual execution time per step (MySQL 8.0.18+)
Slow Query Log	Captures queries exceeding a time threshold
SHOW PROFILE	Detailed timing breakdown of query phases
Performance Schema	Comprehensive runtime statistics

---

Basic Syntax & Rules

EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 1024;

Column	Meaning
id	Query part number
select_type	SIMPLE, SUBQUERY, DERIVED, etc.
table	Which table is being accessed
type	Join type — how MySQL accesses the table (critical!)
possible_keys	Indexes MySQL considered
key	Index MySQL actually chose
key_len	How much of the index is used
ref	What's compared to the index
rows	Estimated number of rows to examine
filtered	Percentage of rows that match the WHERE
Extra	Additional info (Using index, Using filesort, Using temporary)

Join Types — From Best to Worst

Type	Meaning	Performance
system	Table has exactly 1 row	⚡ Fastest
const	Unique index lookup, at most 1 row	⚡ Fastest
eq_ref	One row per join match (unique index)	⚡ Excellent
ref	Non-unique index lookup	✅ Good
range	Index range scan (BETWEEN, >, <)	✅ Good
index	Full index scan (every entry in the index)	⚠️ Moderate
ALL	Full table scan (every row!)	❌ Worst

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1024\G

Unlike regular EXPLAIN, this actually runs the query and shows real execution times:

-> Filter: (orders.customer_id = 1024) (cost=2.50 rows=5) (actual time=0.023..0.045 rows=5 loops=1)
    -> Index lookup on orders using idx_customer_id (customer_id=1024) (cost=2.50 rows=5) (actual time=0.021..0.040 rows=5 loops=1)

---

Step-by-Step Examples

Example 1: Diagnosing a Full Table Scan

Step 1: Run EXPLAIN on a slow query

EXPLAIN SELECT * FROM orders WHERE total_amount > 100;

type	key	rows	Extra
ALL	NULL	250000	Using where

Problem: type = ALL and key = NULL → no index used, scanning all 250,000 rows.

Step 2: Add an index

CREATE INDEX idx_total_amount ON orders(total_amount);

Step 3: Re-run EXPLAIN

EXPLAIN SELECT * FROM orders WHERE total_amount > 100;

type	key	rows	Extra
range	idx_total_amount	45000	Using index condition

Result: type = range, scanning only 45,000 of 250,000 rows (82% reduction).

---

Example 2: EXPLAIN ANALYZE — Before and After

Before indexing:

EXPLAIN ANALYZE
SELECT c.full_name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.country = 'Indonesia'
GROUP BY c.customer_id\G

-> Group aggregate: count(o.order_id)  (actual time=450.2..485.0 rows=1200 loops=1)
    -> Nested loop join  (actual time=0.8..430.5 rows=15000 loops=1)
        -> Filter: (c.country = 'Indonesia')  (actual time=0.3..120.4 rows=1200 loops=1)
            -> Table scan on c  (actual time=0.2..95.0 rows=50000 loops=1)

Problem: Table scan on customers (50,000 rows examined for 1,200 matches).

After adding index:

CREATE INDEX idx_country ON customers(country);

EXPLAIN ANALYZE ... -- same query

-> Group aggregate: count(o.order_id)  (actual time=15.2..25.0 rows=1200 loops=1)
    -> Nested loop join  (actual time=0.3..12.5 rows=15000 loops=1)
        -> Index lookup on c using idx_country (country='Indonesia')  (actual time=0.2..2.4 rows=1200 loops=1)

Result: 485ms → 25ms (19x faster).

---

Example 3: Enabling and Using the Slow Query Log

Step 1: Enable the slow query log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- Log queries slower than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Step 2: Check the log

# View recent slow queries
tail -50 /var/log/mysql/slow.log

# Time: 2026-02-10T14:32:15.123456Z
# User@Host: app_user[app_user] @ [10.0.1.5]
# Query_time: 3.425000  Lock_time: 0.000200  Rows_sent: 150  Rows_examined: 250000
SELECT * FROM orders WHERE total_amount > 100 ORDER BY order_date DESC;

Key metrics:

• Query_time: 3.425 — took 3.4 seconds
• Rows_examined: 250000 — scanned 250K rows to return 150

Step 3: Use mysqldumpslow to summarize

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

This shows the top 10 slowest queries, grouped by pattern.

---

Example 4: Common EXPLAIN Red Flags

EXPLAIN SELECT * FROM orders
WHERE YEAR(order_date) = 2026
ORDER BY total_amount DESC;

type	key	rows	Extra
ALL	NULL	250000	Using where; Using filesort

Two red flags:

1. YEAR(order_date) — applying a function to a column prevents index usage. Fix:

-- Instead of YEAR(order_date) = 2026:
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

2. Using filesort — MySQL sorts results in memory/disk instead of using an index. Fix:

CREATE INDEX idx_date_amount ON orders(order_date, total_amount);

---

Practical Use Cases

1. Pre-Launch Performance Review

Run EXPLAIN on every major query before launching a new feature.

2. Ongoing Performance Monitoring

Enable the slow query log to catch regressions as data grows.

3. Database Migration Validation

After migrating to a new server, compare EXPLAIN plans to verify indexes are intact.

4. Index Impact Analysis

Before adding a new index, run EXPLAIN to verify it will actually be used.

5. Query Optimization

Transform queries based on EXPLAIN feedback — eliminate table scans, filesorts, and temporary tables.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Functions on indexed columns in WHERE	Index not used — full table scan	Rewrite: WHERE col >= value AND col < value
SELECT * when only a few columns needed	Fetches unnecessary data, can't use covering index	Select only needed columns
Missing composite index for multi-column WHERE	Multiple index merge (slower) or table scan	Create a composite index matching the WHERE clause
Ignoring Using temporary in EXPLAIN	Query creates temp table in memory/disk	Optimize GROUP BY or use covering index
Not re-checking EXPLAIN after schema changes	Indexes may be invalidated by ALTER TABLE	Re-run EXPLAIN after any schema change
Trusting rows estimate blindly	Estimates can be significantly wrong	Use EXPLAIN ANALYZE for actual row counts

---

Best Practices

• Run EXPLAIN on every new query — before it hits production
• Use EXPLAIN ANALYZE — for real execution times, not just estimates
• Enable slow query log — catch queries that degrade over time
• Never use functions on indexed columns in WHERE — prevents index usage
• Check for ALL type and Using filesort — most common performance issues
• Create composite indexes matching your WHERE + ORDER BY — one index for the whole query
• Review EXPLAIN after data growth — query plans change as tables grow

---

Hands-On Practice

Exercise 1: Read an EXPLAIN Plan (Easy)

Run EXPLAIN on these queries and identify the join type and key used:

EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
EXPLAIN SELECT * FROM orders WHERE UPPER(status) = 'PENDING';

Why does the third query perform differently?

Exercise 2: Fix a Full Table Scan (Medium)

Given this query:

SELECT customer_id, COUNT(*) FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31' GROUP BY customer_id;

1. Run EXPLAIN — note the join type
2. Create an appropriate index
3. Run EXPLAIN again — verify the improvement
4. Compare estimated vs actual rows with EXPLAIN ANALYZE

Exercise 3: Slow Query Analysis (Advanced)

1. Enable the slow query log with long_query_time = 0.5
2. Run several queries against a large table
3. Use mysqldumpslow to identify the top offenders
4. Optimize the worst query using EXPLAIN and indexing
5. Verify the improvement with EXPLAIN ANALYZE

---

Connection to Other Concepts

Related Concept	How It Connects
Indexes	EXPLAIN reveals whether indexes are being used
Configuration Tuning	Next lesson — buffer pool, thread cache, query cache
Views	Views don't have their own indexes — profile the underlying query
Stored Procedures	Use EXPLAIN on queries inside procedures
Transactions	Long-running queries inside transactions hold locks longer

What to Learn Next

• Server Configuration Tuning — optimize MySQL's internal settings

---

Visual Learning Diagram

flowchart TD
    A["Run EXPLAIN on query"] --> B{"type = ALL?"}
    B -->|Yes| C["🔴 Full table scan\nAdd an index!"]
    B -->|No| D{"Using filesort?"}
    D -->|Yes| E["🟡 In-memory sort\nAdd ORDER BY column to index"]
    D -->|No| F{"Using temporary?"}
    F -->|Yes| G["🟡 Temp table created\nOptimize GROUP BY"]
    F -->|No| H["✅ Query is well-optimized"]

    classDef bad fill:#dc3545,stroke:#fff,color:#fff
    classDef warn fill:#ffc107,stroke:#333,color:#333
    classDef good fill:#28a745,stroke:#fff,color:#fff
    class C bad
    class E,G warn
    class H good

Quick Reference

-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 1024;

-- EXPLAIN ANALYZE (actual timing)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1024\G

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- View current queries
SHOW FULL PROCESSLIST;

-- Index hints
SELECT * FROM orders USE INDEX (idx_date) WHERE order_date > '2026-01-01';
SELECT * FROM orders FORCE INDEX (idx_date) WHERE order_date > '2026-01-01';
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'pending';

What's Next

• Server Configuration Tuning