MySQL Multi‑Table Join Performance: When to Prefer Service‑Layer Merging Over Database Joins

When discussing MySQL versus PostgreSQL multi‑table joins, the author notes that MySQL only supports nested‑loop joins, lacking sort‑merge and hash joins, which can make queries involving more than three tables slower than PostgreSQL.

For small tables (tens of thousands of rows) without indexes, a Cartesian product can explode result size, making multiple single‑table queries more efficient than a single massive join due to network I/O constraints.

In typical business scenarios, join conditions are indexed, producing a small intermediate result that can be fetched with a single RPC when using a database join, saving two extra RPCs compared to service‑layer merging.

However, many systems still prefer to perform the merge in the service layer for three main reasons:

Database CPU is expensive; moving computation to horizontally scalable services improves overall throughput.

Complex architectures often involve multiple databases behind middleware, making cross‑database joins impossible, so a service layer abstracts the data source.

Sharding and distributed transactions limit join feasibility; service‑layer logic can handle eventual consistency and compensate for failed writes.

To improve performance, high‑throughput applications often decompose joins: execute separate single‑table queries and combine results in the application. This approach enhances cache reuse, reduces lock contention, eases sharding, and can even outperform nested‑loop joins by implementing hash‑style association in code.

Example of a full join 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';

Decomposed into three queries:

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);

By splitting the query, applications can cache individual table results, reduce lock competition, and achieve better scalability, especially in sharded environments.

END