Index selection strategy in MySQL - Step-by-Step Execution

Concept Flow - Index selection strategy

Start Query

↓

Check WHERE clause

↓

Are columns indexed?

No→Full Table Scan

Yes↓

Use Index to filter rows

↓

Retrieve matching rows

↓

Return results

The database checks if query columns have indexes to decide between scanning the whole table or using the index to quickly find rows.

Execution Sample

MySQL

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Shows how MySQL chooses to use an index or not for filtering rows by department_id.

Execution Table

Step	Action	Condition/Check	Decision	Result
1	Start query execution	N/A	N/A	Begin processing SELECT
2	Check WHERE clause columns	department_id present	Yes	Proceed to index check
3	Check if department_id is indexed	Index exists on department_id	Yes	Use index for filtering
4	Use index to find matching rows	department_id = 5	Matches found	Retrieve rows via index
5	Return results	N/A	N/A	Output filtered rows
6	End	N/A	N/A	Query complete

💡 Query ends after retrieving rows using index on department_id

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Query State	Not started	WHERE clause identified	Index usage decided	Rows filtered by index	Results returned

Key Moments - 2 Insights

Why does the database sometimes do a full table scan instead of using an index?

If the columns in the WHERE clause are not indexed (see execution_table step 3), the database cannot use an index and must scan the whole table.

How does the database decide which index to use if multiple indexes exist?

The database evaluates which index filters rows most efficiently based on the query conditions (refer to execution_table step 3 and 4). It picks the index that reduces rows scanned the most.

Visual Quiz - 3 Questions

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

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Decision' column in execution_table row for step 3 where index usage is decided.

According to variable_tracker, what is the state of the query after step 4?

A WHERE clause identified

B Rows filtered by index

C Index usage decided

D Results returned

💡 Hint

Look at the 'After Step 4' column for 'Query State' in variable_tracker.

If the department_id column had no index, what would happen according to the concept_flow?

A Full table scan

B Use index for filtering

C Return no results

D Query fails

💡 Hint

See concept_flow where 'Are columns indexed?' leads to 'Full Table Scan' if No.

Concept Snapshot

Index selection strategy:
- Database checks WHERE clause columns
- If columns are indexed, uses index to filter rows
- Otherwise, does full table scan
- Index use speeds up row retrieval
- Choosing best index improves query performance

Full Transcript

When a query runs, the database looks at the WHERE clause to see which columns are used. It then checks if those columns have indexes. If yes, it uses the index to quickly find matching rows. If not, it scans the entire table. Using indexes reduces the number of rows scanned and speeds up queries. The database picks the index that filters rows most efficiently. This process is shown step-by-step in the execution table and variable tracker.