MySQL EXPLAIN Statement

The EXPLAIN statement in MySQL shows the execution plan for a SELECT query. It reveals how MySQL plans to execute the query — which tables are accessed, in what order, which indexes are used, and how many rows are scanned. This information is essential for identifying performance problems and optimising slow queries.

Why Use EXPLAIN?

A query may look correct but run slowly without an obvious reason. EXPLAIN exposes the internal steps MySQL takes — for example, whether it uses an index or performs a full table scan — allowing targeted improvements.

Syntax

EXPLAIN SELECT column1, column2
FROM table_name
WHERE condition;

Sample Setup

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    INDEX idx_department (department)
);

INSERT INTO employees (emp_name, department, salary) VALUES
('Ravi Kumar', 'IT', 72000),
('Sneha Joshi', 'HR', 45000),
('Karan Das', 'IT', 88000),
('Pooja Nair', 'Finance', 61000);

Example: Running EXPLAIN

EXPLAIN SELECT emp_name, salary
FROM employees
WHERE department = 'IT';

Understanding the EXPLAIN Output Columns

Column	Description
id	Query number; higher numbers execute first in subqueries
select_type	Type of SELECT (SIMPLE, PRIMARY, SUBQUERY, etc.)
table	The table being accessed in this step
type	Join type — indicates efficiency (see below)
possible_keys	Indexes MySQL could use
key	The index MySQL actually chose to use
key_len	Length of the index used (smaller can be faster)
rows	Estimated number of rows MySQL will examine
Extra	Additional information about the query execution

The type Column — Access Efficiency

The type column is the most important indicator. From best to worst:

Type	Meaning
system	Table has only one row (best possible)
const	Primary key or unique index lookup — one row result
eq_ref	One row per join from the previous table using unique index
ref	Index lookup returning multiple rows
range	Index scan for a range (BETWEEN, >, <)
index	Full index scan (better than ALL, but still slow)
ALL	Full table scan — worst performance, no index used

Example: Comparing With and Without Index

Without Index (Full Table Scan)

EXPLAIN SELECT * FROM employees WHERE salary = 72000;

Output shows type: ALL and key: NULL — no index is available for salary.

After Adding Index

CREATE INDEX idx_salary ON employees (salary);
EXPLAIN SELECT * FROM employees WHERE salary = 72000;

Output now shows type: ref and key: idx_salary — the index is used.

EXPLAIN with JOIN

EXPLAIN SELECT e.emp_name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.department = d.dept_name;

EXPLAIN shows one row per table accessed. Checking both rows reveals whether indexes are used for both sides of the join.

EXPLAIN FORMAT=JSON

For a more detailed output with cost estimates:

EXPLAIN FORMAT=JSON
SELECT emp_name FROM employees WHERE department = 'IT';

Key Points

EXPLAIN shows the execution plan MySQL uses for a SELECT query.
The type column indicates access efficiency — ALL means full table scan (bad); const or ref means index is used (good).
The key column shows which index (if any) MySQL used.
The rows column shows how many rows MySQL estimates it will scan.
Use EXPLAIN before adding indexes to confirm they will actually be used.

Previous lessons

Back to courses

Next lessons