How to Read EXPLAIN Output in MySQL: Simple Guide

Use the EXPLAIN statement before your query to see how MySQL executes it. The output shows columns like id, select_type, table, type, possible_keys, key, rows, and Extra that explain the query plan and performance details.

Syntax

The EXPLAIN statement is used before a SELECT, DELETE, INSERT, REPLACE, or UPDATE query to show how MySQL executes it. It helps you understand the query plan.

Key columns in the output include:

• id: The query's execution order.
• select_type: The type of SELECT (simple, primary, subquery).
• table: The table accessed.
• type: The join type, showing how tables are joined.
• possible_keys: Indexes MySQL could use.
• key: The index actually used.
• rows: Estimated rows scanned.
• Extra: Additional info like 'Using where' or 'Using index'.

sql

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Example

This example shows how to use EXPLAIN to analyze a query selecting employees from a specific department. It helps identify if indexes are used and how many rows are scanned.

sql

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Output

id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: dept_id_idx
key: dept_id_idx
key_len: 4
ref: const
rows: 10
Extra: Using where

Common Pitfalls

Common mistakes when reading EXPLAIN output include:

• Ignoring the type column, which shows join quality; ALL means a full table scan and is slow.
• Not checking if the key column is NULL, meaning no index is used.
• Overlooking the rows column, which estimates how many rows MySQL scans; high numbers can slow queries.
• Misinterpreting Extra info; for example, Using temporary or Using filesort can indicate slow operations.

Example of a less efficient query and a better one:

sql

/* Less efficient: no index used */
EXPLAIN SELECT * FROM employees WHERE last_name LIKE '%son';

/* More efficient: index used on last_name prefix */
EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'Son%';

Quick Reference

Column	Meaning	Notes
id	Query execution order	Higher means later execution
select_type	Type of SELECT	SIMPLE, PRIMARY, SUBQUERY, etc.
table	Table accessed	Name of the table or alias
type	Join type	ALL (full scan), ref, eq_ref, const (best)
possible_keys	Indexes MySQL could use	Shows candidate indexes
key	Index actually used	NULL means no index used
rows	Estimated rows scanned	Lower is better for performance
Extra	Additional info	Using where, Using index, Using temporary

Key Takeaways

Use EXPLAIN before your query to see how MySQL plans to execute it.

Focus on the 'type' and 'key' columns to understand index usage and join quality.

High 'rows' values and 'ALL' in 'type' often mean slow queries.

Check the 'Extra' column for clues about temporary tables or filesorts.

Optimize queries by ensuring indexes are used and avoiding full table scans.