Why High Traffic Makes SQL JOINs a Bottleneck

In a system with a sales‑product table (SPU level), a SKU table, and an activity table, the author initially used a three‑table JOIN that performed well under normal load because all tables were indexed.

After a sudden traffic surge, the connection pool ran out of available database connections, causing every request that touched the database to experience extremely long response times and eventually making the whole system unavailable.

The root cause is that each request thread must acquire a connection, execute the JOIN, and hold the connection until the query finishes. A JOIN on three tables forces the engine to read three separate B+‑tree indexes, which takes longer than reading a single index regardless of the query’s logical efficiency. The author measured that three concurrent threads each spent about 0.2 seconds executing the JOIN, occupying three connections for that duration.

Because the connections remain occupied, additional incoming threads cannot obtain a connection, leading to rapid pool exhaustion under high load.

Switching to separate single‑table queries changed the pattern: each thread read one table at a time, taking roughly 0.01 seconds per query. The connection is released almost immediately, allowing other threads to acquire it quickly and preventing pool depletion.

For interviewers asking about SQL JOINs, explain that JOINs are fine when traffic and data volume are low, but under heavy traffic they can become a bottleneck because they read multiple B+ trees and hold connections longer, exhausting the pool.