JOIN performance considerations in MySQL - Step-by-Step Execution

Concept Flow - JOIN performance considerations

Start Query

↓

Parse JOIN Condition

↓

Check Indexes on Join Columns

↓

Use Index

↓

Join Rows Efficiently

↓

Return Result Set

This flow shows how a JOIN query is processed, focusing on whether indexes exist on join columns to improve performance.

Execution Sample

MySQL

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'USA';

This query joins orders with customers on customer_id and filters customers from the USA.

Execution Table

Step	Action	Index Used?	Rows Scanned	Result
1	Parse JOIN condition orders.customer_id = customers.id	N/A	N/A	Ready to join
2	Check index on orders.customer_id	Yes	1000 (using index)	Efficient lookup
3	Check index on customers.id	Yes	100 (using index)	Efficient lookup
4	Filter customers by country = 'USA'	Yes (index on country)	20	Filtered customers
5	Join filtered customers with orders using indexes	Yes	200	Joined rows
6	Return final result set	N/A	200	Result with joined rows
7	Exit	N/A	N/A	Query complete

💡 Indexes on join columns and filter column reduce scanned rows, improving performance.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
Rows Scanned on orders	N/A	1000	1000	200	200
Rows Scanned on customers	N/A	100	20	20	20
Result Rows	N/A	N/A	N/A	200	200

Key Moments - 3 Insights

Why does having an index on the join columns matter?

Indexes allow the database to quickly find matching rows instead of scanning the entire table, as shown in steps 2 and 3 where indexes reduce rows scanned.

What happens if there is no index on the filter column (customers.country)?

Without an index, the database scans all customer rows to filter, increasing rows scanned and slowing the join, unlike step 4 where an index reduces rows to 20.

Why does the number of rows scanned decrease after filtering customers?

Filtering customers by country reduces the dataset before joining, so fewer rows need to be matched in step 5, improving performance.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are scanned on the customers table after filtering by country?

A 100

B 200

C 20

D 1000

💡 Hint

Check the 'Rows Scanned' column at step 4 in the execution table.

At which step does the query use indexes on both join columns?

A Step 1

B Step 3

C Step 5

D Step 7

💡 Hint

Look for 'Check index on customers.id' in the execution table.

If there was no index on orders.customer_id, what would likely happen to rows scanned at step 2?

A Rows scanned would increase to full table scan

B Rows scanned would stay the same

C Rows scanned would decrease

D Query would fail

💡 Hint

Refer to the decision branch in the concept flow about index presence.

Concept Snapshot

JOIN performance depends on indexes on join columns.
Indexes reduce rows scanned by enabling fast lookups.
Filtering before joining reduces data to process.
Without indexes, full table scans slow queries.
Always check indexes on join and filter columns.

Full Transcript

This visual execution shows how a JOIN query runs in MySQL focusing on performance. The query joins orders and customers on customer_id and filters customers by country. The database first parses the join condition, then checks for indexes on join columns. If indexes exist, it uses them to scan fewer rows. Filtering customers by country uses an index to reduce rows from 100 to 20 before joining. The join then matches 200 rows efficiently using indexes. The final result set contains 200 joined rows. Key points are that indexes on join and filter columns reduce scanned rows and improve speed. Without indexes, the database would scan entire tables, slowing the query. This step-by-step trace helps beginners see how indexes affect join performance.