Understanding MySQL Multi‑Table Join Performance Compared to PostgreSQL and Query Decomposition Strategies

When discussing database join performance with other DBAs, it was noted that MySQL only supports nested‑loop joins, lacking sort‑merge and hash joins that PostgreSQL provides, which makes MySQL less efficient for multi‑table queries involving more than three tables.

The author summarizes two key points: keeping join depth under three layers improves efficiency, and a more generic approach better prepares systems for distributed architectures.

In practice, a multi‑table join in MySQL may be slower than executing multiple single‑table queries, especially when the join results explode due to missing indexes or Cartesian products, causing network I/O bottlenecks.

Many businesses therefore move join logic to the service layer: first fetch a small result set from one table, use it to query the next table via RPC, and finally merge results in the service, reducing the number of RPC calls compared to naïve join execution.

Three common reasons for preferring service‑side joins are:

Database CPU resources are expensive; offloading computation to horizontally scalable services improves overall throughput.

Heterogeneous databases and middleware often cannot perform cross‑database joins, so a service abstraction reduces coupling.

Sharding and partitioning make in‑database joins difficult unless the joined tables reside in the same physical shard.

Query decomposition is a technique used by high‑performance applications: instead of a single join query, execute separate single‑table queries and combine results in the application. 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 three independent 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);

Although this increases the number of queries, it offers several advantages:

Higher cache hit rates for individual table queries.

Reduced lock contention on the database.

Easier horizontal scaling and sharding.

Potential overall query performance improvement.

Less redundant data retrieval.

In some scenarios, application‑level hash joins outperform MySQL’s nested‑loop joins.

The article concludes that decomposing joins and handling association logic in the service layer can lead to better performance, scalability, and maintainability for large‑scale systems.