One important query design question is whether it's preferable to break up a complex query into several simpler queries. The traditional approach to database design emphasizes doing as much work as possible with as few queries as possible. This approach was historically better because of the cost of network communication and the overhead of the query parsing and optimization stages.
It's still a good idea to use as few queries as possible, but sometimes you can make a query more efficient by decomposing it and executing a few simple queries instead of one complex one. Don't be afraid to do this; weigh the costs, and go with the strategy that causes less work.
That said, using too many queries is a common mistake in application design. For example, some applications perform N single-row queries to retrieve data from a table when they could use a single N-row query.
However, several simpler queries can actually give significant performance advantages:
- Can sometimes reduce lock contention (such as table locks)
- The queries themselves can be more efficient
- Easier to scale the database by placing tables on different servers
- Caching in application can be more efficient
- Can reduce redundant row accesses
- Improve to design index better