Parting Shots

At the most general abstraction, one could say that the trick to speeding up joins is to give the DBMS lots of informationbut not so much that it will be confused about which information to use. We've seen this over and over in this chapterfor example, sometimes we've said "Index here to improve the query," and in other cases, the advice has been "Don't index here because it will result in a useless path that you don't want the optimizer to take." So we can't leave you with some simple formula that will always speed up your joins. But there are some items that we do want to stress:

• Expect nested- loops .

• The best nested-loop is the one where all inner-table criteria are met.

• The most likely joins should turn out to be foreign-key/primary-key joins.

This is not really the end of our joining story. The favorite question is still to come in the next chapterShould I join or should I subquery?