MySQL EXPLAIN Plan Analysis – Understand & Optimize Your Queries

Introduction – Why Use EXPLAIN in MySQL?

The EXPLAIN command in MySQL reveals how your query will be executed—which indexes will be used, how tables will be joined, and how many rows will be scanned. It’s an essential tool for debugging slow queries, optimizing joins, and verifying that indexes are effective.

In this guide, you’ll learn:

• How to use EXPLAIN and EXPLAIN ANALYZE
• Column-by-column breakdown of the output
• Real-world use cases for performance tuning
• What to fix and how to fix it
• Best practices for using EXPLAIN with JOINs, subqueries, and sorting

Basic Syntax

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

With Execution Time (MySQL 8+):

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN Output Columns Breakdown

Understanding type – The Most Important Column

Sample EXPLAIN Output

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Interpretation:

• Good: Index on customer_id used
• ref: Means efficient indexed lookup
• rows = 10: Only 10 rows expected to be scanned

Example with JOINs

EXPLAIN SELECT o.id, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.order_date > '2024-01-01';

Watch for:

• type = ref or eq_ref on JOIN
• key should be not NULL
• Avoid ALL scans on joined tables

🚨 Common Red Flags in Extra Column

Use EXPLAIN ANALYZE (MySQL 8+)

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Returns the execution plan with actual run-time metrics, including:

• Execution time per operation
• Loops
• Number of rows examined vs returned

Best Practices for EXPLAIN Optimization

Avoid ALL type — means full table scan
Use EXPLAIN on all slow queries
Check that indexes are being used (key not NULL)
Add indexes on JOIN and WHERE columns
Avoid functions on indexed columns (e.g., YEAR(date_column) in WHERE)
Check rows count to ensure minimal data scan
Use LIMIT and ORDER BY on indexed fields when paginating

EXPLAIN with Advanced Queries

Subqueries

EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Consider using a JOIN instead of a subquery for better performance.

GROUP BY + HAVING

EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

Add indexes on the GROUP BY column to avoid temp tables and filesorts.

Summary – Recap & Next Steps

The EXPLAIN and EXPLAIN ANALYZE commands are your primary tools for diagnosing slow queries and understanding how MySQL executes them. Use them consistently to optimize queries and indexes.

Key Takeaways

• Use EXPLAIN to see execution plans before running queries
• Look for type, key, rows, and Extra info
• Avoid full table scans (ALL) and filesorts where possible
• Use EXPLAIN ANALYZE in MySQL 8+ for real execution time

Real-World Relevance
Developers and DBAs rely on EXPLAIN to optimize APIs, reports, dashboards, and background tasks using MySQL at scale.

FAQ – MySQL EXPLAIN Plan

What’s the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows estimated plans, EXPLAIN ANALYZE shows actual runtime execution (MySQL 8+).

Why is my query using type = ALL?
MySQL is performing a full table scan—likely missing an index. Add an index on filtered/joined column.

How can I improve Using filesort?
Add appropriate ORDER BY indexes or simplify sorting logic.

What if key is NULL in EXPLAIN?
No index is used. Check possible_keys and create one if necessary.

Should I always add indexes if ALL is shown?
Usually yes, but analyze the use case—small tables may not need indexing.