Why indexes matter in SQL - Visual Breakdown

Concept Flow - Why indexes matter

Start Query

↓

Scan Table Rows

↓

Check Each Row for Condition

↓

Return Matching Rows

↓

Start Query

↓

Use Index to Find Rows

↓

Directly Access Matching Rows

↓

Return Matching Rows

This flow shows how a query searches data without and with an index, highlighting how indexes speed up finding rows.

Execution Sample

SQL

SELECT * FROM employees WHERE employee_id = 5;

This query finds the employee with ID 5, showing how an index helps find it faster.

Execution Table

Step	Action	Rows Checked	Index Used	Result
1	Start query execution	0	No	No rows found yet
2	Scan row 1	1	No	Check if employee_id=5 (No)
3	Scan row 2	2	No	Check if employee_id=5 (No)
4	Scan row 3	3	No	Check if employee_id=5 (No)
5	Scan row 4	4	No	Check if employee_id=5 (No)
6	Scan row 5	5	No	Check if employee_id=5 (Yes) - Return row
7	End query	5	No	Returned 1 row
1	Start query execution	0	Yes	No rows found yet
2	Use index to find employee_id=5	0	Yes	Directly locate row 5
3	Access row 5	1	Yes	Return row immediately
4	End query	1	Yes	Returned 1 row

💡 Without index, all rows scanned until match found; with index, direct access to matching row.

Variable Tracker

Variable	Start	After Step 2	After Step 5	Final
Rows Checked	0	1	4	5
Index Used	No	No	No	No
Rows Returned	0	0	0	1

Key Moments - 2 Insights

Why does scanning all rows take longer than using an index?

Because scanning checks each row one by one (see execution_table rows 2-6), while using an index jumps directly to the matching row (rows 2-3 with index).

Does using an index mean fewer rows are checked?

Yes, with an index only the matching row is accessed (execution_table row 3 with index), unlike scanning all rows without index.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows are checked when using the index?

A 0

B 1

C 5

D All rows

💡 Hint

Check the 'Rows Checked' column in the rows where 'Index Used' is Yes.

At which step does the query find the matching row without using an index?

A Step 6

B Step 2

C Step 7

D Step 3

💡 Hint

Look at the 'Result' column for the first 'Yes' match without index.

If the table had 1000 rows, how would using an index affect the number of rows checked?

A Still checks all 1000 rows

B Checks half the rows

C Checks fewer rows, close to 1

D Checks twice as many rows

💡 Hint

Indexes allow direct access, so rows checked stay low regardless of table size.

Concept Snapshot

Why Indexes Matter:
- Without index: query scans every row to find matches.
- With index: query uses index to jump directly to matching rows.
- Indexes reduce rows checked, speeding up queries.
- Especially helpful for large tables.
- Use indexes on columns often searched or joined.

Full Transcript

This visual shows how a database query finds data with and without an index. Without an index, the query checks each row one by one until it finds the match, which can be slow for big tables. With an index, the query uses a special data structure to jump directly to the matching row, checking far fewer rows and returning results faster. The execution table traces each step, showing rows checked and whether the index is used. This helps beginners see why indexes matter for query speed.