When to Use MySQL Multi‑Table Joins vs. Service‑Layer Joins: Performance and Architectural Trade‑offs

MySQL only supports a single join algorithm—nested‑loop join ( nested-loop )—and does not provide sort‑merge join ( sort-merge join ) or hash join ( hash join ) that PostgreSQL supports, making complex multi‑table queries (more than three tables) less efficient in MySQL.

When two large tables without indexes are joined using a Cartesian product, the result set can explode to billions of rows, turning network I/O into a bottleneck; in such cases fetching two smaller result sets and merging them in the service layer can be faster than a single massive join.

Many production systems prefer moving the join logic to the service layer for three main reasons: (1) database CPU is expensive and shared between reads and writes, so off‑loading computation to horizontally scalable services reduces DB load; (2) heterogeneous databases and middleware often cannot perform cross‑database joins, so a service abstraction decouples the application from the storage layer; (3) sharding and partitioning limit join feasibility unless both tables reside in the same physical shard.

One practical approach is to decompose a complex join into several single‑table queries. For example, the original query: select * from tag join tag_post on tag_post.tag_id=tag.id join post on tag_post.post_id=post.id where tag.tag='mysql'; can be rewritten as: Select * from tag where tag='mysql'; Select * from tag_post where tag_id=1234; Select * from post where id in (123,456,567,9989,8909); This yields the same result set while allowing each sub‑query to be cached and optimized individually.

Decomposing joins brings several advantages: higher cache hit rates for rarely‑changing tables, reduced lock contention, easier horizontal scaling and database splitting, potential query performance gains, fewer redundant rows, and the ability to implement hash‑style joins in the application layer, which can be faster for certain workloads.

The article also clarifies the concept of RPC (Remote Procedure Call) as the mechanism used when the service layer performs these separate queries.

Overall, the discussion encourages developers to evaluate the trade‑offs between database‑level joins and service‑level composition, especially in high‑traffic, sharded, or multi‑database environments.