SQL Notes
Learn SQL EXPLAIN Plan in detail, understand query execution plans, table scans, index scans, index seeks, query cost analysis, and performance optimization.
You already know about:
Query Optimization
Query Optimizer
Table Scan
Index Seek
Performance TuningOne of the key 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 some of the most powerful performance tuning tools in SQL.
Simple Definition
EXPLAIN is a command that displays the execution plan chosen by the query optimizer.
Why is EXPLAIN Important?
imagine 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 Used
Understanding 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 Rows
Fine.
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 Index
helps 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 Row
only.
Important Column: key
Shows:
Index UsedExample:
PRIMARY
IX_EmployeeIDExample
EXPLAIN
SELECT *
FROM Employees
WHERE EmployeeID = 10;Output:
key = PRIMARY
Meaning:
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 Operations
for 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 = 100000
Meaning:
Full Table ScanCreating Index
CREATE INDEX IX_Email
ON Employees(Email);Running EXPLAIN Again
Output:
type = REF key = IX_Email rows = 1
Much better.
EXPLAIN for JOINs
Query:
SELECT *
FROM Employees E
JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;EXPLAIN shows:
Join Order Indexes Used Access Method
for 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 Filesort
indicating extra sorting work.
EXPLAIN and GROUP BY
Query:
SELECT
DepartmentID,
COUNT(*)
FROM Employees
GROUP BY DepartmentID;Possible output:
Using Temporary
meaning temporary tables are involved.
EXPLAIN ANALYZE
Modern databases support:
EXPLAIN ANALYZEDifference:
EXPLAIN
Shows:
Estimated Plan
EXPLAIN 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 Usage
on:
AccountIDReal-World Example: E-Commerce
Query:
SELECT *
FROM Products
WHERE CategoryID = 5;EXPLAIN identifies:
Table Scan Or Index Usage
Real-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\'s 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\'s EXPLAIN? ANALYZE?
A command that shows actual execution statistics.
Summary
EXPLAIN Plan is one of the key 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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, optimization, explain, plan
Related SQL Complete Guide Topics