Why indexes speed up queries in MySQL - Visual Breakdown

Concept Flow - Why indexes speed up queries

Start Query

↓

Check for Index

↓

Use Index

↓

Find Rows

↓

Return Result

↓

End

The database checks if an index exists for the query. If yes, it uses the index to find rows faster; if no, it scans the whole table.

Execution Sample

MySQL

SELECT * FROM users WHERE age = 30;
-- Assume 'age' column has an index

This query finds all users aged 30 using the index on the 'age' column to speed up search.

Execution Table

Step	Action	Index Used?	Rows Checked	Result
1	Start query execution	N/A	0	No rows yet
2	Check if 'age' column has index	Yes	0	Index found
3	Use index to find matching rows	Yes	Few (only matching age=30)	Rows found quickly
4	Return matching rows	Yes	Few	Result returned
5	End query	N/A	Few	Query complete

💡 Query ends after using index to quickly find matching rows instead of scanning whole table

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Index Used	No	Yes	Yes	Yes	Yes
Rows Checked	0	0	Few (matching rows)	Few	Few
Rows Returned	0	0	Few	Few	Few

Key Moments - 2 Insights

Why does using an index check fewer rows than scanning the whole table?

Because the index stores sorted pointers to rows, the database can jump directly to matching rows instead of looking at every row (see execution_table step 3).

What happens if there is no index on the searched column?

The database must scan every row in the table to find matches, which is slower (see concept_flow path 'No' leading to 'Full Table Scan').

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database decide to use the index?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Check the 'Index Used?' column at each step in execution_table

According to variable_tracker, how many rows are checked after using the index?

A Few rows matching the condition

B All rows in the table

C No rows

D Exactly one row

💡 Hint

Look at 'Rows Checked' values after Step 3 in variable_tracker

If the 'age' column had no index, what would change in the execution flow?

A The database would still use the index

B The database would perform a full table scan

C The query would return no results

D The query would run faster

💡 Hint

Refer to concept_flow where 'No' index leads to 'Full Table Scan'

Concept Snapshot

Why indexes speed up queries:
- Indexes are like a book's index, pointing directly to data.
- Database checks for index before searching.
- Using index means fewer rows checked.
- Without index, full table scan is done.
- Indexes make queries faster by reducing search work.

Full Transcript

When a database runs a query, it first checks if there is an index on the column used in the search condition. If an index exists, the database uses it to quickly find matching rows without scanning the entire table. This is like using a book's index to find a topic instead of reading every page. The execution table shows the steps: starting the query, checking for an index, using the index to find rows, returning results, and ending the query. The variable tracker shows that after using the index, only a few rows are checked and returned. If no index exists, the database must scan every row, which is slower. Understanding this helps beginners see why indexes speed up queries.