Why Avoid Multi‑Table Joins and Optimize with Hash Join in MySQL

Introduction

Three years ago I worked with an architect who embedded a lot of business logic directly in SQL, creating extremely complex multi‑table joins that were hard to maintain. Recently I encountered a similar situation where a colleague wrote massive joins that even the database could not handle. Therefore we aim to keep SQL simple and avoid excessive joins.

Why Multi‑Table JOINs Are Discouraged

The primary reason is lower efficiency. MySQL implements joins using a Nested-Loop Join algorithm, which requires two nested loops: the outer loop scans the first table, and for each row the inner loop scans the second table, comparing rows to produce matches.

Performance Issues:

Complex joins increase query complexity and can cause performance degradation, especially with large data sets.

More rows and columns are processed, leading to higher I/O and memory consumption.

Readability and Maintainability:

Complex JOIN statements become difficult to understand, raising maintenance costs.

Frequent query modifications become error‑prone when joins are intricate.

Index Utilization:

Joins may prevent the optimizer from using indexes effectively.

If join columns lack proper indexes, performance drops sharply.

Lock Contention:

Multi‑table joins can hold row or table locks longer, increasing lock contention and hurting concurrency.

Data Integrity:

Complex joins can mask data inconsistencies, making debugging harder.

Ensuring that joined results satisfy business logic becomes more difficult.

Optimization Strategies

Query Decomposition: Retrieve data in separate queries and perform the association in application memory.

Data Redundancy: In some cases, duplicate data to reduce the need for joins.

Wide Tables: Flatten related tables into a single wide table, optionally syncing to Elasticsearch or querying directly.

What Is a Hash Join? (Extended Reading)

Before MySQL 8.0.18, joins were implemented with the Nested‑Loop Join algorithm.

Nested‑Loop Join: For two tables the complexity can reach O(n^2) , for three tables O(n^3) , causing exponential performance loss as data grows.

MySQL 8.0.18 introduced the hash join algorithm, which is more efficient for large data sets.

Hash Join Principle

A hash join works by building a hash table from one (usually smaller) table and probing it with rows from the other table.

Build Phase:

Select a small table (the build table) and create a hash table based on the join key.

Compute the hash value for each key and store the key‑row mapping in the hash table.

Probe Phase:

Scan the probe table row by row.

For each row, compute the hash of the join key and look up matching rows in the hash table.

If a match is found, combine the rows into the result set.

Hash Join Example

Assume two tables A and B that we want to join on the ID column.

Step 1: Build the Hash Table

For ID = 1 , hash value hash(1) stores {1: Alice}.

For ID = 2 , hash value hash(2) stores {2: Bob}.

For ID = 3 , hash value hash(3) stores {3: Charlie}.

Resulting hash table:

{
  1: Alice,
  2: Bob,
  3: Charlie
}

Step 2: Probe Phase

Probe table B row with ID = 1 : hash(1) matches, result (1, Alice, 25).

Probe table B row with ID = 2 : hash(2) matches, result (2, Bob, 30).

Probe table B row with ID = 4 : hash(4) not found, no match.

After matching, aggregation yields the final result set.

The hash table resides in memory; if it exceeds a threshold, MySQL falls back to a disk‑based hash join.

Disk‑Based Hash Join

If the driving table exceeds the memory threshold, MySQL partitions it into multiple hash buckets stored on disk. Each bucket is loaded into memory sequentially, probed, then released before loading the next bucket, allowing processing of data larger than available memory.

Conclusion

In practice, minimize multi‑table joins to keep SQL logic clear, which improves performance and eases maintenance.

Official MySQL documentation for Hash JOIN:

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html