SQL Performance Tuning

There seems to be a Law of DBMS Development that statesIn version 1.0 the subquery support will be bad or nonexistent, and it will slowly get better.

Witness the IBM texts that tell you how bad subqueries were in the early days, and the fact that Sybase used to transform almost all subqueries to joins, and the fact that MySQL was seven years old before its developers announced a beta version with subquery support. The result is that subqueries get a bad reputation, which persists even after the product matures. In this chapter, we're going to tell you that subqueries are fast and reliable nowadaysbut we expect you to be skeptical. So we'll take a hard look at the current state of affairs, performance-wise.

The topic of subqueries also tends to bring up several operating questions. When is it better to use a join rather than a subquery, and how would I flatten a query? What would induce a DBMS to choose a particular plan? What design and syntax choices can I exploit? We'll look at each one of these questions as well.