MySQL EXPLAIN Command – Complete Guide with Real Examples

EXPLAIN shows how MySQL executes a query — the execution plan.

MySQL EXPLAIN Command – Complete Guide with Real Examples

Complete Guide with Real Examples

MySQL `EXPLAIN` Command – Complete Guide with Real Examples

Master Query Optimization Like a Pro

What is `EXPLAIN`?

EXPLAIN shows how MySQL executes a query — the execution plan.

It tells you:
- Which indexes are used
- Join order and type
- Row estimates
- Potential bottlenecks

Basic Syntax

EXPLAIN SELECT ... FROM ...;
-- or
EXPLAIN FORMAT=JSON SELECT ...;
-- or (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT ...;

Sample Tables

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    country VARCHAR(10),
    created_at DATETIME,
    INDEX idx_email (email),
    INDEX idx_country_created (country, created_at)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    status ENUM('pending','shipped'),
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_status (user_id, status)
);

Insert sample data:

INSERT INTO users (name, email, country, created_at) VALUES
('Alice', 'a@x.com', 'US', '2025-01-01'),
('Bob', 'b@x.com', 'UK', '2025-01-02'),
('Charlie', 'c@x.com', 'US', '2025-01-03');

INSERT INTO orders (user_id, amount, status) VALUES
(1, 99.99, 'shipped'),
(1, 149.50, 'pending'),
(2, 79.00, 'shipped');

Example 1: Simple SELECT with Index

EXPLAIN SELECT * FROM users WHERE email = 'a@x.com';

Output:

id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra
---|-------------|-------|------------|------|---------------|-----------|---------|-------|------|----------|------------
1  | SIMPLE      | users | NULL       | ref  | idx_email     | idx_email | 102     | const | 1    | 100.00   | Using index condition

Interpretation:

Column	Meaning
`type = ref`	Good! Uses index for equality
`key = idx_email`	Index used
`rows = 1`	Estimates 1 row
`Extra: Using index condition`	Index used to filter

Fast query

Example 2: Full Table Scan (Bad!)

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

Output:

id | select_type | table | type | possible_keys | key  | rows | Extra
---|-------------|-------|------|---------------|------|------|------
1  | SIMPLE      | users | ALL  | NULL          | NULL | 3    | Using where

Interpretation:

type = ALL → Full table scan (slow!)
key = NULL → No index used
rows = 3 → Scans all rows

Fix: CREATE INDEX idx_name ON users(name);

Example 3: Composite Index Usage

EXPLAIN SELECT * FROM users 
WHERE country = 'US' AND created_at > '2025-01-01';

Output:

id | table | type | possible_keys            | key                    | rows | Extra
---|-------|------|--------------------------|------------------------|------|------
1  | users | range| idx_country_created      | idx_country_created    | 2    | Using index condition

Good: Uses idx_country_created for both conditions

Example 4: Leftmost Prefix Rule (Partial Use)

EXPLAIN SELECT * FROM users WHERE created_at > '2025-01-01';

Output:

id | table | type | possible_keys            | key  | rows | Extra
---|-------|------|--------------------------|------|------|------
1  | users | ALL  | NULL                     | NULL | 3    | Using where

Bad: created_at is 2nd in (country, created_at) → not used

Fix:

CREATE INDEX idx_created_country ON users(created_at, country);

Example 5: JOIN Execution Plan

EXPLAIN SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'shipped';

Output:

id | select_type | table | type | possible_keys        | key              | rows | Extra
---|-------------|-------|------|----------------------|------------------|------|------
1  | SIMPLE      | o     | ref  | idx_user_status      | idx_user_status  | 1    | Using where
1  | SIMPLE      | u     | eq_ref | PRIMARY            | PRIMARY          | 1    | NULL

Interpretation:

Starts with orders (driving table)
Uses idx_user_status → finds shipped orders
Then eq_ref on users.id → 1 row per match

Excellent plan

Example 6: Covering Index (Index-Only Scan)

EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1;

Output:

id | table | type   | possible_keys    | key             | rows | Extra
---|-------|--------|------------------|-----------------|------|------------
1  | orders| ref    | idx_user_status  | idx_user_status | 2    | **Using index**

Using index → No table access → Fastest possible

Example 7: ORDER BY with Index

EXPLAIN SELECT * FROM users 
WHERE country = 'US' 
ORDER BY created_at DESC;

With proper index:

CREATE INDEX idx_country_created ON users(country, created_at DESC);

Output:

id | table | type  | key                    | Extra
---|-------|-------|------------------------|------
1  | users | ref   | idx_country_created    | Using where; Using index

No filesort → Index provides order

Example 8: Bad Query – Filesort

EXPLAIN SELECT * FROM users ORDER BY name;

Output:

id | table | type | key  | Extra
---|-------|------|------|--------------------
1  | users | ALL  | NULL | **Using filesort**

Using filesort → Sorts in memory/disk → Slow

Fix:

CREATE INDEX idx_name ON users(name);

Key `type` Values (From Best to Worst)

Type	Meaning	Speed
`system`	1 row, const table	Fastest
`const`	PK/UNIQUE match	Fastest
`eq_ref`	Unique join (1 row)	Fastest
`ref`	Non-unique index	Fast
`range`	Index range scan	Good
`index`	Full index scan	OK
`ALL`	Full table scan	Slow

Key `Extra` Values (Watch Out!)

Extra	Meaning
`Using index`	Covering index → Great!
`Using where`	Filter after scan
`Using temporary`	Temp table (bad for large data)
`Using filesort`	Sort not from index → Slow
`Using index condition`	ICP – pushes filter to storage engine

EXPLAIN FORMAT=JSON (Deep Dive)

EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE country = 'US';

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "users",
      "access_type": "ref",
      "possible_keys": ["idx_country_created"],
      "key": "idx_country_created",
      "rows_examined_per_scan": 2,
      "cost_info": { "read_cost": "0.40", "total_cost": "0.80" }
    }
  }
}

Use for tools, scripts, advanced analysis

EXPLAIN ANALYZE (MySQL 8.0.18+) – Actual Performance

EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = 'a@x.com';

Output:

-> Filter: (users.email = 'a@x.com')  (cost=0.35 rows=1) 
   -> Index lookup on users using idx_email (email='a@x.com')  
       (cost=0.35 rows=1) (actual time=0.00021..0.00023 rows=1 loops=1)

Shows actual time, rows, loops

How to Use `EXPLAIN` in Practice

Step-by-Step Optimization Workflow

Run EXPLAIN on slow query
Look for:
type = ALL
key = NULL
Using filesort
High rows
Add/fix index
Re-run EXPLAIN
Compare rows, type, Extra

Pro Tips

Tip	Command
Always `EXPLAIN` before production	`EXPLAIN SELECT ...`
Use `EXPLAIN ANALYZE` in dev	Real execution time
Avoid `SELECT *`	Hurts covering indexes
Index foreign keys	Speeds up JOINs
Use `FORMAT=TREE` (MySQL 8.0.16+)	Human-readable plan

EXPLAIN FORMAT=TREE SELECT ...;

Quick Cheat Sheet

Goal	Check in EXPLAIN
Index used?	`key` column
Full scan?	`type = ALL`
Covering index?	`Extra: Using index`
Sort from index?	No `Using filesort`
Join efficient?	`eq_ref` or `ref`

Practice Queries – Run & EXPLAIN

1. EXPLAIN SELECT * FROM users WHERE id = 1;
2. EXPLAIN SELECT name FROM users WHERE country = 'US';
3. EXPLAIN SELECT * FROM orders ORDER BY amount DESC;
4. EXPLAIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

Final Words

Every slow query starts with EXPLAIN.
Every fast query ends with EXPLAIN.

Master EXPLAIN → 10x better performance

Resources

MySQL EXPLAIN Docs
Use The Index, Luke!
Tools: MySQL Workbench, pt-query-digest, EverSQL

Want a downloadable EXPLAIN cheat sheet (PDF)?
Or a script to auto-analyze slow queries? Just ask!

Last updated: Nov 09, 2025

MySQL EXPLAIN Command – Complete Guide with Real Examples

EXPLAIN shows how MySQL executes a query — the execution plan.