Analyzing the Execution Process and Cost of Three‑Table JOIN in MySQL

MySQL’s three‑table JOIN is not performed by first joining the first two tables and then the third; instead it uses a nested‑loop (or block‑nested‑loop) algorithm that repeatedly scans tables based on the driver table’s rows.

1 Premise

To observe row scans clearly, the example forces the optimizer to use the index‑nested‑loop algorithm on tables without indexes on the join columns.

set optimizer_switch='block_nested_loop=off';

Three tables (t1, t2, t3) are created, populated, and then joined with the query:

select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a<21;

2 Scanning‑Row Analysis

The slow‑log shows 24,100 rows examined. The EXPLAIN output confirms an index‑nested‑loop plan, and the row breakdown is:

t1 scans 100 rows (driver table).

t3 scans 20 × 200 = 4,000 rows (driven table).

t2 scans 20 × 1,000 = 20,000 rows (driven table).

Total rows = 100 + 4,000 + 20,000 = 24,100.

3 Execution‑Cost Analysis

Using MySQL 5.7 cost constants (io_block_read_cost = 1.0, row_evaluate_cost = 0.2), the article computes IO and CPU costs for each table:

t1 : IO = 1 × 1.0 = 1, CPU = 100 × 0.2 = 20 → total = 21.

t3 : IO = 1 × 1.0 = 1, CPU = 200 × 0.2 = 40 → total = 820 (20 × (1+40)).

t2 : IO = 4 × 1.0 = 4, CPU = 1,000 × 0.2 = 200 → total = 81,600 (400 × (4+200)).

Overall cost ≈ 82,441.

4 Summary

For multi‑table JOINs, the nested‑loop and “join first two tables then the third” approaches have identical costs; however, when join columns lack indexes, the optimizer’s fan‑out estimates can be far off, leading to misleading row‑count statistics. Using proper indexes (preferably unique or high‑cardinality) mitigates this, and MySQL 8.0’s HASH JOIN further reduces such estimation errors.