EXPLAIN query analysis in MySQL - Step-by-Step Execution

Concept Flow - EXPLAIN query analysis

Write SQL Query

↓

Run EXPLAIN on Query

↓

MySQL Analyzes Query

↓

Output Query Plan

↓

Review Execution Details

↓

Optimize Query Based on Plan

You write a SQL query, run EXPLAIN on it, MySQL shows how it plans to run it, then you use that info to improve your query.

Execution Sample

MySQL

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

This command shows how MySQL plans to execute the SELECT query filtering employees by department.

Execution Table

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	department_id	department_id	4	const	10	Using where
-	-	-	-	-	-	-	-	-	Query plan shows MySQL uses index on department_id to find 10 rows matching condition

💡 EXPLAIN output ends after showing the query plan details for the given query.

Variable Tracker

Variable	Start	After EXPLAIN
id	N/A	1
select_type	N/A	SIMPLE
table	N/A	employees
type	N/A	ref
possible_keys	N/A	department_id
key	N/A	department_id
key_len	N/A	4
ref	N/A	const
rows	N/A	10
Extra	N/A	Using where

Key Moments - 3 Insights

Why does the 'type' column say 'ref' instead of 'ALL'?

Because the execution_table shows MySQL uses an index (key 'department_id') to find matching rows, so it scans only relevant rows, not the whole table ('ALL').

What does 'Using where' in the Extra column mean?

'Using where' means MySQL applies the WHERE condition to filter rows after using the index, as shown in the last row of execution_table.

Why is 'rows' estimated as 10?

The 'rows' column estimates how many rows MySQL expects to examine, here 10, based on index statistics, as seen in the execution_table row.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'key' used by MySQL?

A PRIMARY

B department_id

C NULL

D employees

💡 Hint

Check the 'key' column in the first row of the execution_table.

At which step does MySQL apply the WHERE condition according to the EXPLAIN output?

A Before using the index

B After scanning all rows

C After using the index (Using where)

D It does not apply WHERE

💡 Hint

Look at the 'Extra' column in the execution_table for the phrase 'Using where'.

If the 'type' was 'ALL' instead of 'ref', what would that mean?

A MySQL scans the entire table

B MySQL uses an index to find rows

C MySQL uses a temporary table

D MySQL uses a join

💡 Hint

Refer to the 'type' column meaning in the execution_table and key_moments explanations.

Concept Snapshot

EXPLAIN shows how MySQL runs a query.
It outputs a table with columns like id, type, key, rows.
'key' shows index used; 'type' shows scan method.
'Extra' gives extra info like 'Using where'.
Use EXPLAIN to find slow parts and optimize queries.

Full Transcript

The EXPLAIN command in MySQL helps you see how the database plans to run your SQL query. When you write a query and run EXPLAIN before it, MySQL returns a table describing each step it will take. This includes which table it reads, what type of scan it uses (like index or full scan), which index it uses, how many rows it expects to check, and extra notes like if it applies a WHERE filter. For example, if the 'type' column says 'ref', it means MySQL uses an index to find rows efficiently. If it says 'ALL', it scans the whole table, which is slower. The 'Extra' column can say 'Using where' meaning it filters rows after using the index. By reading this output, you can understand if your query is efficient or if it needs optimization. This visual trace shows the step-by-step output of EXPLAIN for a simple SELECT query filtering by department_id, helping beginners see how MySQL executes queries internally.