? MySQL EXPLAIN Plan

? Quick Overview

The EXPLAIN statement provides a look under the hood of how MySQL executes your SQL queries. It is the primary diagnostic tool for understanding performance, revealing whether the database is using indexes effectively or performing slow full-table scans.

? Key Concepts

Query Plan: A roadmap the database engine generates to fetch data.
Index Usage: Determines if the query uses specific keys to find rows quickly.
Scan Type: Distinguishes between scanning every row (slow) and jumping to specific rows (fast).
Supported Statements: Works with SELECT, DELETE, INSERT, UPDATE.

? Syntax / Theory

Prepend the keyword EXPLAIN to your query. MySQL will process the statement and return details about the execution plan instead of the result set.

? View Code Example

-- Standard syntax for analyzing a SELECT statement
EXPLAIN SELECT * FROM users WHERE email = 'jdoe@example.com';

-- FORMAT=JSON gives more detailed, machine-readable output
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE id = 105;

? Code Example(s)

Below is an example comparing a query that uses an index versus one that might trigger a table scan.

? View Code Example

-- Scenario: We want to find employees in a specific department
-- Case 1: If 'department_id' is indexed, this is fast (ref)
EXPLAIN SELECT name, role FROM employees WHERE department_id = 5;

-- Case 2: If searching by a non-indexed column like 'bio', this is slow (ALL)
EXPLAIN SELECT name FROM employees WHERE bio LIKE '%developer%';

? Live Output / Explanation

Example Output Table

When you run EXPLAIN, you receive a result table like this:

id	select_type	table	type	key	rows
1	SIMPLE	employees	ref	idx_dept	12

Crucial Columns to Watch:

type: The join type.
- ALL: Full Table Scan (Worst performance).
- index: Full Index Scan (Poor).
- range: Scans a range of rows (Good).
- ref: Matches specific rows using an index (Better).
- const/eq_ref: Reads one row (Best).
key: The actual index MySQL decided to use. If NULL, no index was used.
rows: Estimate of how many rows MySQL must examine to find the result. Lower is better.

✅ Tips & Best Practices

Target 'type': Aim for ref, range, or const. If you see ALL on a large table, you likely need an index.
Check 'key': Verify that the index you created is actually the one appearing in the key column.
Cardinality matters: Indexes work best on columns with many unique values.
Compound Indexes: Ensure the order of columns in your WHERE clause matches your composite index (Leftmost Prefix Rule).

? Try It Yourself

Run a query on a large table with WHERE column = 'value' and check the type.
Add an index to that column: CREATE INDEX idx_name ON table(column);
Run EXPLAIN again and observe how the type changes from ALL to ref and rows decreases drastically.