How to Use EXPLAIN in MySQL to Analyze Queries

Use the EXPLAIN keyword before your SQL query in MySQL to see how the database executes it. This shows details like which indexes are used and the order of table reads, helping you optimize query performance.

Syntax

The basic syntax to use EXPLAIN in MySQL is to place it before your SELECT, DELETE, INSERT, REPLACE, or UPDATE query. It returns a row-by-row description of how MySQL plans to execute the query.

• EXPLAIN: The keyword to request the query plan.
• your_query: The SQL statement you want to analyze.

sql

EXPLAIN your_query;

Example

This example shows how to use EXPLAIN with a simple SELECT query to see how MySQL reads the employees table.

sql

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Output

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

Common Pitfalls

Common mistakes when using EXPLAIN include:

• Running EXPLAIN on queries without indexes, which shows full table scans and poor performance.
• Misinterpreting the type column; for example, ALL means a full scan, which is slow.
• Forgetting to analyze the Extra column for important notes like Using filesort or Using temporary, which indicate potential slowdowns.

Always check if indexes exist on columns used in WHERE or JOIN clauses to improve the plan.

sql

/* Wrong: No index on department_id */
EXPLAIN SELECT * FROM employees WHERE department_id = 5;

/* Right: Add index first */
CREATE INDEX dept_id_idx ON employees(department_id);
EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Quick Reference

Column	Description
id	Query sequence number in complex queries
select_type	Type of SELECT (e.g., SIMPLE, PRIMARY)
table	Table name being accessed
type	Join type or access method (e.g., ALL, ref, eq_ref)
possible_keys	Indexes MySQL could use
key	Index actually used
key_len	Length of the key used
ref	Columns or constants compared to the key
rows	Estimated rows to examine
Extra	Additional info (e.g., Using where, Using filesort)

Key Takeaways

Use EXPLAIN before your query to see how MySQL executes it and identify performance issues.

Check the 'type' and 'key' columns to understand if indexes are used effectively.

Add indexes on columns used in WHERE or JOIN clauses to improve query plans.

Pay attention to the 'Extra' column for warnings about sorting or temporary tables.

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.