Understanding MySQL Join Types and Optimization Techniques

1. Simple Nested Loop Join

MySQL may choose a driver table and for each row of that table scan the entire join table, resulting in O(M×N) comparisons; this approach is the least efficient, especially for deep pagination.

2. Index Nested Loop Join

When the join column of the inner table has an index, MySQL uses the index to locate matching rows, retrieves the rowid, and then performs a random‑IO row lookup. The Multi‑Range Read (MRR) optimization can batch rowids, sort them, and read them sequentially, turning random I/O into more efficient sequential I/O. To enable MRR, set mrr_cost_based = false.

If MRR is active, the Extra column in EXPLAIN output will contain the MRR information.

3. Blocked Nested Loop Join

When no suitable index exists, MySQL caches the driver‑table join column values in the join buffer and compares them in batches, also caching the selected columns to avoid extra lookups. This reduces I/O compared with a pure nested loop join.

The presence of a join buffer is indicated in the Extra column of EXPLAIN. The buffer size can be inspected with:

SHOW VARIABLES LIKE '%join_buffer_size%';

4. Joins Not Supported by MySQL

MySQL does not implement Hash Join or Merge Join. A Hash Join builds an in‑memory hash table on the smaller input and probes it with the larger input, which works only for equality joins and can be memory‑intensive. A Merge Join requires both inputs to be sorted (typically via indexes); if sorting is needed, the cost can be high.

5. Driver Table Optimization

The optimizer prefers the smallest table—after applying filters—as the driver. Mis‑estimation can cause a large table to be chosen, leading to inefficient use of indexes and additional sorting. You can force a specific driver table or index with STRAIGHT_JOIN or FORCE INDEX hints.

SELECT
    t1.`isbn`,
    `title`,
    `sub_title`
FROM
    `tb_book_base` t1
    INNER JOIN `tb_book_main` t2 ON t1.`isbn` = t2.`isbn`
WHERE
    t2.`code` = (SELECT `code` FROM tb_book_press WHERE `name` = '6215981117')
    AND t1.publish_time > 20150101
ORDER BY
    t1.`publish_time`;

Using STRAIGHT_JOIN or forcing an index on the large table can ensure the optimizer uses the desired driver table and avoids costly temporary tables or file sorts.

Conclusion

The article consolidates practical knowledge about MySQL join algorithms, their performance characteristics, and tuning methods such as MRR, BKA, join buffers, and driver‑table hints, providing a reference for developers dealing with large‑scale data sets.