EXPLAIN in MySQL for Reading Query Execution Plans

EXPLAIN has appeared throughout this entire module as the go-to diagnostic tool, but so far only at a surface level. This lesson goes deep into actually reading EXPLAIN's output, column by column, turning it from a vague 'run this before optimizing' habit into a precise diagnostic skill for understanding exactly how MySQL plans to execute any given query.

Key Definitions

• Execution plan: The step-by-step strategy MySQL's query optimizer chooses to actually retrieve the data a query requests, including which indexes (if any) to use and in what order tables are accessed.
• Access type (type column): EXPLAIN's indication of the general strategy used to access a table's rows, ranging from a full table scan (ALL) to a highly efficient constant lookup (const).

What You'll Learn

• Interpret every major column of MySQL's EXPLAIN output.
• Distinguish between different access types (ALL, index, range, ref, const).
• Use the rows and key columns to estimate a query's actual efficiency.
• Read the Extra column for critical diagnostic signals like 'Using filesort'.

Detailed Explanation

Running EXPLAIN before any SELECT query returns a table describing MySQL's planned execution strategy, and several columns deserve close attention. The type column indicates the access method, ranging from worst to best: ALL (a full table scan, checking every row), index (scanning the entire index rather than the table, better than ALL but still examining everything), range (using an index to scan only a bounded range of rows, such as a BETWEEN condition), ref (using a non-unique index to find matching rows, common for JOIN conditions and equality filters), and const (the fastest type, used when MySQL can resolve the query to a single row via a unique index or primary key lookup).

The key column shows which index MySQL actually decided to use for that table — if this is NULL despite an index existing on a relevant column, it's a strong signal MySQL judged the index unhelpful for this particular query, often due to low cardinality or other factors covered in Lesson 9.11. The rows column estimates how many rows MySQL expects to examine to satisfy the query at that step — lower is better, and a large discrepancy between this estimate and the table's total row count is a strong performance signal.

The Extra column carries several critical diagnostic messages: 'Using index' (covered in Lesson 9.13) indicates a covering index was used; 'Using filesort' indicates MySQL had to perform an additional, potentially expensive sorting step because no index could satisfy the requested ORDER BY directly; and 'Using temporary' indicates MySQL had to create a temporary table internally to complete the query, often associated with certain GROUP BY or DISTINCT operations that couldn't be resolved directly through available indexes. Both 'Using filesort' and 'Using temporary' are common signals worth investigating further when optimizing a genuinely slow query.

Visual Summary

A horizontal spectrum diagram labeled 'EXPLAIN type column — worst to best': [ALL: full table scan] --> [index: full index scan] --> [range: bounded index range] --> [ref: non-unique index lookup] --> [const: single-row unique lookup], with a color gradient from red (ALL) to green (const).

Quick Reference

EXPLAIN Column	What It Tells You
type	The access strategy: ALL (worst) through const (best)
key	Which index MySQL actually chose to use, or NULL if none
rows	Estimated number of rows MySQL expects to examine
Extra: Using index	The query was fully satisfied by a covering index
Extra: Using filesort	MySQL had to perform an additional, potentially costly sort step
Extra: Using temporary	MySQL had to build an internal temporary table to complete the query

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

CREATE INDEX idx_appointments_doctor ON appointments(doctor_id);

-- A well-optimized query using an equality lookup on an indexed column
EXPLAIN SELECT * FROM appointments WHERE doctor_id = 101;
-- Expect: type = ref, key = idx_appointments_doctor, relatively low 'rows'

-- A query requiring a sort MySQL cannot satisfy from any index
EXPLAIN SELECT * FROM appointments ORDER BY status, appointment_date;
-- Likely shows: Extra = Using filesort (no index covers this exact sort order)

-- A full table scan on an unindexed column
EXPLAIN SELECT * FROM appointments WHERE status = 'Completed';
-- Likely shows: type = ALL, key = NULL, 'rows' close to the table's total row count

The first EXPLAIN shows an efficient ref-type lookup using idx_appointments_doctor, examining only the small number of rows actually matching doctor_id = 101. The second query's ORDER BY on status and appointment_date together likely triggers 'Using filesort' in the Extra column, since no existing index covers exactly that sort combination, forcing MySQL to sort the results as an additional step after retrieving them. The third query, filtering on the unindexed status column, shows type = ALL and key = NULL, confirming a full table scan is being performed, with the rows estimate close to the table's entire row count.

Real-World Examples

• Database performance engineers use EXPLAIN as the very first diagnostic step for any reported slow query, before considering any other optimization approach.
• Automated query performance monitoring tools parse EXPLAIN output programmatically to flag queries showing 'type: ALL' or 'Using filesort' on large tables as candidates for review.
• Technical interviews for backend and database roles frequently ask candidates to interpret sample EXPLAIN output directly, testing this exact diagnostic reading skill.

Common Mistakes to Avoid

• Only glancing at whether EXPLAIN 'shows something' without actually reading and interpreting the type, key, rows, and Extra columns in detail.
• Assuming a NULL value in the key column always means something is broken, rather than considering it might reflect a deliberate, correct optimizer decision.
• Ignoring 'Using filesort' or 'Using temporary' warnings in the Extra column, missing valuable optimization opportunities they point toward.

Interview Questions

Q1. What does the 'type' column in EXPLAIN output indicate, and what are its key values from worst to best?

The type column indicates the access strategy MySQL uses for a table, ranging from ALL (a full table scan, the worst) through index, range, ref, up to const (a single-row lookup via a unique index, the best), each representing progressively more efficient row access.

Q2. What does it mean if EXPLAIN's key column shows NULL for a table that has a relevant index?

It means MySQL's optimizer decided not to use that index for this particular query, often because the column has low cardinality or the optimizer estimated a full table scan would actually be faster, a scenario covered in the discussion of when indexes help versus hurt.

Q3. What does 'Using filesort' in EXPLAIN's Extra column signal?

It signals that MySQL could not satisfy the query's requested ORDER BY directly through an available index's sorted structure, and had to perform an additional, potentially expensive explicit sorting step after retrieving the data.

Practice MCQs

1. Which EXPLAIN 'type' value represents the most efficient access strategy?

1. ALL
2. index
3. range
4. const

Answer: D. const

Explanation: const represents the most efficient access type, used when MySQL can resolve the query to exactly one row via a unique index or primary key lookup.

2. What does 'Using filesort' in EXPLAIN's Extra column indicate?

1. The query used a covering index
2. MySQL had to perform an additional sorting step outside of any index
3. The query failed to execute
4. A temporary table was created for GROUP BY

Answer: B. MySQL had to perform an additional sorting step outside of any index

Explanation: 'Using filesort' specifically indicates MySQL could not use an index's sorted order to satisfy the query's ORDER BY, requiring an extra, potentially costly sort operation.

Quick Revision Points

• EXPLAIN's type column ranges from ALL (worst, full scan) to const (best, single-row lookup) — a frequently tested ordering.
• The key column shows which index was actually used, or NULL if none was chosen.
• 'Using filesort' and 'Using temporary' in the Extra column are important, commonly tested diagnostic signals.

Conclusion

• EXPLAIN's type column reveals the access strategy, ranging from full scans (ALL) to efficient single-row lookups (const).
• The key and rows columns show exactly which index was used and how many rows MySQL expects to examine.
• The Extra column surfaces critical signals like 'Using filesort' and 'Using temporary' that point toward specific optimization opportunities.

Reading MySQL's EXPLAIN output in depth turns it from a vague diagnostic habit into a precise skill: the type column reveals the access strategy used, ranging from a full table scan (ALL) to an efficient single-row lookup (const); the key column shows exactly which index, if any, MySQL chose to use; the rows column estimates how many rows must be examined; and the Extra column surfaces critical signals like 'Using index' (a covering index was used), 'Using filesort' (an additional sort step was required), and 'Using temporary' (an internal temporary table was needed). Together, these columns provide the complete diagnostic picture needed to genuinely understand and optimize how any given query actually executes.