[jrumbut]

But, besides index hints, the developers don't need to worry about those things if they use the database to perform joins. The database management system chooses for them and does it pretty well (counterproductive index hints are not unheard of).

If they do it in application code, then they probably ought to learn about fancy sorting and joining algorithms.

But they should really just do it in the database (using read only replicas if the load gets high).

[winrid]

Using read replicas isn't always an option depending on your write throughput and consistency requirements.

The database can only do so well (and will spend a lot of CPU cycles working on your crazy query plan, because getting it wrong is more expensive, so now you effectively limit capacity regardless of how good your storage engine is).

Joins are great, tons of research went into making joins work, and lots of different join algorithms and optimizations based on data sizes, indexes, etc. But you really have to be careful, verses just denormalizing data across multiple tables/collections. Most applications are read-heavy, anyway... I generally plan for things to be successful, in which case joins don't usually work in the hot path.