EXPLAIN plan for query analysis in SQL - Step-by-Step Execution

Concept Flow - EXPLAIN plan for query analysis

Start: Query Submitted

↓

Parse Query

↓

Generate Execution Plan

↓

Show Plan Details

↓

Analyze Plan for Optimization

The EXPLAIN command takes a SQL query, parses it, creates a plan for how the database will run it, then shows this plan so you can understand and improve the query.

Execution Sample

SQL

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

This command shows how the database plans to find employees in the Sales department.

Execution Table

Step	Action	Details	Result
1	Parse Query	Check syntax and structure	Query is valid
2	Generate Plan	Decide scan type (e.g., full table scan or index scan)	Plan created with full table scan
3	Estimate Cost	Calculate cost and rows expected	Cost=100, Rows=50
4	Show Plan	Display plan steps	Output plan with scan type and filters
5	End	Plan ready for review	Execution plan displayed

💡 Plan generation completes after showing the execution steps and cost estimates.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Query	Raw SQL text	Parsed query tree	Execution plan structure	Cost and row estimates added	Plan ready for display
Plan Details	None	None	Scan type: full table scan	Cost=100, Rows=50	Displayed to user

Key Moments - 2 Insights

Why does EXPLAIN show a full table scan instead of using an index?

Because the query condition or table statistics suggest scanning the whole table is cheaper; see execution_table step 2 where scan type is chosen.

What does the cost number in the plan mean?

It estimates how much work the database expects to do; lower cost means faster query. This is shown in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the action in step 3?

A Estimate Cost

B Show Plan

C Parse Query

D Generate Plan

💡 Hint

Check the 'Action' column in execution_table row with Step 3.

At which step does the plan get displayed to the user?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

Look for 'Show Plan' action in execution_table.

If the query had an index on department, which step would most likely change?

A Step 1: Parse Query

B Step 2: Generate Plan

C Step 3: Estimate Cost

D Step 5: End

💡 Hint

Scan type choice happens in step 2, see execution_table.

Concept Snapshot

EXPLAIN shows how the database plans to run a query.
It parses the query, creates a plan, estimates cost, and displays steps.
Use it to find slow parts and improve queries.
Key parts: scan type, cost, rows estimated.
Run: EXPLAIN SELECT ...

Full Transcript

The EXPLAIN plan command helps you see how a database will run your SQL query. When you run EXPLAIN with a query, the database first checks the query's syntax and structure. Then it creates an execution plan, deciding how to find the data, like whether to scan the whole table or use an index. It estimates the cost and number of rows it expects to process. Finally, it shows you this plan so you can understand and optimize your query. For example, if EXPLAIN shows a full table scan, it means the database thinks scanning all rows is cheaper than using an index. The cost number tells you how much work the database expects to do; lower is better. By reading the EXPLAIN output, you can find slow parts and improve your SQL queries.