SQL Topics
EXPLAIN Plan
title: EXPLAIN Plan
In the previous lesson, you learned about:
Query Optimization
Query Optimizer
Table Scan
Index Seek
Performance TuningOne of the most important tools used for query optimization is:
EXPLAIN PlanWriting an optimized query is impossible if you do not understand:
How SQL Executes The QueryThe EXPLAIN Plan allows developers to see:
Execution Strategy
Indexes Used
Join Methods
Query Cost
Estimated Rowsbefore the query is executed.
This makes EXPLAIN one of the most powerful performance tuning tools in SQL.
What is EXPLAIN Plan?
An EXPLAIN Plan shows how the database intends to execute a SQL query.
It provides information about:
Access Methods
Indexes
Joins
Sorting
Filtering
Estimated CostThink of EXPLAIN as:
Query Blueprintshowing the execution steps before actual execution.
Simple Definition
EXPLAIN is a command that displays the execution plan chosen by the query optimizer.
Why is EXPLAIN Important?
Suppose we execute:
SELECT *
FROM Employees
WHERE EmployeeID = 100;Question:
Will SQL Use An Index?
Or Scan Entire Table?Without EXPLAIN:
UnknownWith EXPLAIN:
Execution Details VisibleHow Query Execution Works
SQL Query
↓
Parser
↓
Query Optimizer
↓
Execution Plan
↓
Execution Engine
↓
ResultEXPLAIN shows:
Execution Planstage.
Basic EXPLAIN Syntax
MySQL:
EXPLAIN
SELECT *
FROM Employees;PostgreSQL:
EXPLAIN
SELECT *
FROM Employees;SQL Server:
SET SHOWPLAN_ALL ON;or graphical execution plans.
Example
EXPLAIN
SELECT *
FROM Employees
WHERE EmployeeID = 10;Typical Output
| id | type | table | key |
|---|---|---|---|
| 1 | const | Employees | PRIMARY |
This indicates:
Primary Key UsedUnderstanding EXPLAIN Output
Different databases provide different columns.
Common information includes:
Table Access Method
Indexes Used
Estimated Rows
Cost
Join TypeImportant Column: table
Shows:
Which Table Is AccessedExample:
EmployeesImportant Column: type
Shows access strategy.
Examples:
ALL
INDEX
RANGE
REF
EQ_REF
CONST
SYSTEMType = ALL
Means:
Full Table ScanExample:
SELECT *
FROM Employees;SQL reads every row.
Why ALL is Expensive
For:
100 RowsFine.
For:
100 Million RowsVery expensive.
Type = INDEX
Means:
Full Index ScanBetter than table scan.
Still may be expensive.
Type = RANGE
Used for:
WHERE Salary > 50000or
BETWEENqueries.
Example:
SELECT *
FROM Employees
WHERE Salary
BETWEEN 50000
AND 100000;Type = REF
Used when:
Non-Unique Indexhelps retrieve matching rows.
Type = EQ_REF
Very efficient join access.
Usually appears when:
Primary Key
Foreign Keyrelationships exist.
Type = CONST
One of the fastest access methods.
Occurs when:
WHERE PrimaryKey = ValueExample:
SELECT *
FROM Employees
WHERE EmployeeID = 1;Type = SYSTEM
Fastest possible access.
Occurs when table contains:
One Rowonly.
Important Column: key
Shows:
Index UsedExample:
PRIMARY
IX_EmployeeIDExample
EXPLAIN
SELECT *
FROM Employees
WHERE EmployeeID = 10;Output:
key = PRIMARYMeaning:
Primary Key Index UsedImportant Column: rows
Shows:
Estimated Rowsthat SQL expects to examine.
Example:
rows = 1Excellent.
Example:
rows = 1000000Potential performance issue.
Important Column: filtered
Shows:
Percentage Of Rows
Expected To Matchafter filtering.
Important Column: Extra
Provides additional details.
Common values:
Using where
Using index
Using filesort
Using temporaryUsing Where
Means:
Filter Appliedafter reading rows.
Using Index
Means:
Covering Index UsedExcellent performance indicator.
Using Filesort
Means:
Extra Sorting RequiredExample:
ORDER BY Salarymay trigger filesort.
Why Filesort Can Be Expensive
Large datasets require:
Memory
CPU
Disk Operationsfor sorting.
Using Temporary
Means:
Temporary Table Createdduring query execution.
Often occurs with:
GROUP BY
ORDER BY
DISTINCTEXPLAIN Example Without Index
Query:
SELECT *
FROM Employees
WHERE Email =
'abc@gmail.com';No index exists.
Output:
type = ALL
rows = 100000Meaning:
Full Table ScanCreating Index
CREATE INDEX IX_Email
ON Employees(Email);Running EXPLAIN Again
Output:
type = REF
key = IX_Email
rows = 1Much better.
EXPLAIN for JOINs
Query:
SELECT *
FROM Employees E
JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;EXPLAIN shows:
Join Order
Indexes Used
Access Methodfor each table.
Join Analysis
EXPLAIN helps identify:
Missing Join Indexes
Expensive Joins
Wrong Join OrderEXPLAIN and ORDER BY
Query:
SELECT *
FROM Employees
ORDER BY Salary;Possible output:
Using Filesortindicating extra sorting work.
EXPLAIN and GROUP BY
Query:
SELECT
DepartmentID,
COUNT(*)
FROM Employees
GROUP BY DepartmentID;Possible output:
Using Temporarymeaning temporary tables are involved.
EXPLAIN ANALYZE
Modern databases support:
EXPLAIN ANALYZEDifference:
EXPLAIN
Shows:
Estimated PlanEXPLAIN ANALYZE
Shows:
Actual Execution
Actual Time
Actual RowsExample
PostgreSQL:
EXPLAIN ANALYZE
SELECT *
FROM Employees;Real-World Example: Banking
Query:
SELECT *
FROM Transactions
WHERE AccountID = 1001;EXPLAIN confirms:
Index Usageon:
AccountIDReal-World Example: E-Commerce
Query:
SELECT *
FROM Products
WHERE CategoryID = 5;EXPLAIN identifies:
Table Scan
Or
Index UsageReal-World Example: Payroll
Query:
SELECT *
FROM Employees
WHERE DepartmentID = 10;EXPLAIN verifies department indexes.
Real-World Example: University
Query:
SELECT *
FROM Students
WHERE RollNumber = 101;EXPLAIN confirms primary key access.
Advantages of EXPLAIN
Reveals Query Strategy
Shows how SQL executes queries.
Helps Identify Bottlenecks
Detects slow operations.
Shows Index Usage
Critical for optimization.
Supports Performance Tuning
Primary optimization tool.
Improves Scalability
Helps design efficient systems.
Disadvantages
Database-Specific Output
Different databases show different plans.
Requires Interpretation
Beginners may find plans confusing.
Estimates May Differ
Estimated rows are not always exact.
Complex Plans
Large queries generate complex plans.
Common Mistakes
Ignoring EXPLAIN
Optimization becomes difficult.
Focusing Only On Query Syntax
Execution behavior matters more.
Missing Index Analysis
Indexes significantly affect plans.
Ignoring Rows Examined
Large row counts indicate issues.
Best Practices
Run EXPLAIN Before Optimization
Understand current behavior.
Monitor Rows Examined
Lower is generally better.
Check Index Usage
Avoid unnecessary table scans.
Investigate Filesorts
Reduce expensive sorting.
Compare Plans Before And After Changes
Verify improvements.
Common Interview Questions
What is EXPLAIN?
A command that shows how a query will be executed.
Why is EXPLAIN important?
It helps optimize query performance.
What does type = ALL mean?
Full table scan.
What does key represent?
The index used by the query.
What is EXPLAIN ANALYZE?
A command that shows actual execution statistics.
Summary
EXPLAIN Plan is one of the most important SQL performance analysis tools. It shows how the database optimizer intends to execute a query, including index usage, access methods, joins, sorting, and estimated costs. Understanding EXPLAIN is essential for query optimization and database performance tuning.
In this lesson, you learned:
- What EXPLAIN Plan is
- Query Execution Process
- EXPLAIN Syntax
- Access Types
- Index Analysis
- Rows Examined
- Filesort
- Temporary Tables
- EXPLAIN ANALYZE
- Performance Tuning
Mastering EXPLAIN Plans is a critical skill for database administrators, backend developers, and performance engineers.
Next Step
Continue to the next lesson:
Execution Plan →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for EXPLAIN Plan.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, optimization, explain
Related SQL Topics