Interpreting EXPLAIN Output (type, key, rows, Extra)0%

Identifying & Fixing Slow Queries & Filesort

Interpreting EXPLAIN Output (type, key, rows, Extra)

Advanced14 min read•Updated: 2026-09-12

Interpreting EXPLAIN Output (type, key, rows, Extra)

Reading traditional tabular EXPLAIN output is an essential skill for database engineers. The output table contains 12 columns, but four columns tell 90% of the optimization story: type, key, rows, and Extra.

1. The `type` Column: The Access Method (Best to Worst)

The type column describes how MySQL joins or scans table rows. Your primary objective in query optimization is moving from red/slow access types toward green/fast access types:

`type` Value	Rating	Meaning
`system` / `const`	Best	Table has 1 row, or matched on Primary Key / Unique index equality.
`eq_ref`	Best Join	One row read from this table for each combination from preceding table (PK/Unique join).
`ref`	Great	Non-unique index lookup (multiple rows match the key).
`range`	Good	Index range scan using `BETWEEN`, `>`, `<`, `IN()`.
`index`	Slow	Full Index Scan (scans entire B+Tree from start to finish).
`ALL`	Worst	Full Table Scan! Reads every data block on disk. Critical bottleneck!

2. The `key` and `possible_keys` Columns

possible_keys: The list of indexes MySQL could potentially choose to resolve the query.
key: The actual index chosen by the cost-based optimizer.
If key is NULL, MySQL found no usable index and performed a Full Table Scan (type: ALL).

3. The `rows` and `filtered` Columns

rows: The estimated number of rows MySQL expects to inspect.
filtered: The estimated percentage of inspected rows that will satisfy remaining table conditions (100% is ideal; 5% indicates 95% of inspected rows are discarded!).

$$ ext{Rows Passed to Next Stage} = ext{rows} imes rac{ ext{filtered}}{100}$$

4. The `Extra` Column: Flags of Honor & Red Flags

The Extra column contains critical optimization flags:

Positive Flags (Optimized):

Using index: Covering index! Data retrieved entirely from index B+Tree without touching table rows.
Using index condition: Index Condition Pushdown is active.

Negative Flags (Performance Killers!):

Using filesort: MySQL cannot use index ordering and must perform an extra sorting pass in memory/disk.
Using temporary: MySQL must create an internal temporary table on disk or memory to hold intermediate results (frequent with messy GROUP BY and DISTINCT).
Using where: Rows are filtered after being read from storage, rather than using an index range directly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which value in EXPLAIN's type column indicates the worst possible access method (a Full Table Scan)?

Question 2

In join operations, what is the most optimal access type when joining on a Primary Key or unique non-null column?

Question 3

What does Using filesort in the Extra column mean?

Question 4

What does it mean if possible_keys lists two indexes, but key is NULL?

Question 5

What does Using temporary in the Extra column signal?

Next Lesson

Identifying & Fixing Slow Queries & Filesort

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE	Identifying & Fixing Slow Queries & Filesort

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Identifying & Fixing Slow Queries & Filesort

Interpreting EXPLAIN Output (type, key, rows, Extra)

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Interpreting EXPLAIN Output (type, key, rows, Extra)

1. The `type` Column: The Access Method (Best to Worst)

The type column describes how MySQL joins or scans table rows. Your primary objective in query optimization is moving from red/slow access types toward green/fast access types:

`type` Value	Rating	Meaning
`system` / `const`	Best	Table has 1 row, or matched on Primary Key / Unique index equality.
`eq_ref`	Best Join	One row read from this table for each combination from preceding table (PK/Unique join).
`ref`	Great	Non-unique index lookup (multiple rows match the key).
`range`	Good	Index range scan using `BETWEEN`, `>`, `<`, `IN()`.
`index`	Slow	Full Index Scan (scans entire B+Tree from start to finish).
`ALL`	Worst	Full Table Scan! Reads every data block on disk. Critical bottleneck!

2. The `key` and `possible_keys` Columns

possible_keys: The list of indexes MySQL could potentially choose to resolve the query.
key: The actual index chosen by the cost-based optimizer.
If key is NULL, MySQL found no usable index and performed a Full Table Scan (type: ALL).

3. The `rows` and `filtered` Columns

rows: The estimated number of rows MySQL expects to inspect.
filtered: The estimated percentage of inspected rows that will satisfy remaining table conditions (100% is ideal; 5% indicates 95% of inspected rows are discarded!).

$$ ext{Rows Passed to Next Stage} = ext{rows} imes rac{ ext{filtered}}{100}$$

4. The `Extra` Column: Flags of Honor & Red Flags

The Extra column contains critical optimization flags:

Positive Flags (Optimized):

Using index: Covering index! Data retrieved entirely from index B+Tree without touching table rows.
Using index condition: Index Condition Pushdown is active.

Negative Flags (Performance Killers!):

Using filesort: MySQL cannot use index ordering and must perform an extra sorting pass in memory/disk.
Using temporary: MySQL must create an internal temporary table on disk or memory to hold intermediate results (frequent with messy GROUP BY and DISTINCT).
Using where: Rows are filtered after being read from storage, rather than using an index range directly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which value in EXPLAIN's type column indicates the worst possible access method (a Full Table Scan)?

Question 2

In join operations, what is the most optimal access type when joining on a Primary Key or unique non-null column?

Question 3

What does Using filesort in the Extra column mean?

Question 4

What does it mean if possible_keys lists two indexes, but key is NULL?

Question 5

What does Using temporary in the Extra column signal?

Next Lesson

Identifying & Fixing Slow Queries & Filesort

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE	Identifying & Fixing Slow Queries & Filesort

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Interpreting EXPLAIN Output (type, key, rows, Extra)

1. The type Column: The Access Method (Best to Worst)

2. The key and possible_keys Columns

3. The rows and filtered Columns

4. The Extra Column: Flags of Honor & Red Flags

Positive Flags (Optimized):

Negative Flags (Performance Killers!):

Multiple Choice Questions

Identifying & Fixing Slow Queries & Filesort

Related Lessons

Practice Quiz

Loading Page Content...

Interpreting EXPLAIN Output (type, key, rows, Extra)

1. The type Column: The Access Method (Best to Worst)

2. The key and possible_keys Columns

3. The rows and filtered Columns

4. The Extra Column: Flags of Honor & Red Flags

Positive Flags (Optimized):

Negative Flags (Performance Killers!):

Multiple Choice Questions

Identifying & Fixing Slow Queries & Filesort

Related Lessons

Practice Quiz

1. The `type` Column: The Access Method (Best to Worst)

2. The `key` and `possible_keys` Columns

3. The `rows` and `filtered` Columns

4. The `Extra` Column: Flags of Honor & Red Flags

1. The `type` Column: The Access Method (Best to Worst)

2. The `key` and `possible_keys` Columns

3. The `rows` and `filtered` Columns

4. The `Extra` Column: Flags of Honor & Red Flags