Understanding MySQL’s Nested Loop Join: How It Works and Why Indexes Matter

MySQL implements only one join algorithm, the well‑known Nested Loop Join.

The algorithm scans the left (outer) table row by row, and for each row it compares the join condition with every row of the right (inner) table. If N matching rows are found, the outer row is combined with each of the N rows to produce N result rows. When a third table participates, the result of the first two tables becomes the new outer set, and the process repeats.

Example with three tables: user_group (user groups), group_message (group messages), and group_message_content (message content). To retrieve the message list for a specific user, the following query is executed:

SELECT m.subject msg_subject, c.content msg_content
FROM user_group g, group_message m, group_message_content c
WHERE g.user_id = 1
  AND m.group_id = g.group_id
  AND c.group_msg_id = m.id;

The query proceeds in three steps:

Find the rows in user_group where user_id = 1.

Use the group_id values from the previous result to loop through group_message.

Join the intermediate result with group_message_content by matching group_msg_id to id from group_message.

Because each step can use an index, the execution is fast. If the index on group_message_content.group_msg_id is removed, step three becomes a full table scan of group_message_content, relying on the join buffer to mitigate the performance impact.

This demonstrates why correctly defining indexes is essential for efficient join queries in MySQL.