Lessons Learned
The power of SQL comes from its being a descriptive language rather than a procedural language. A SQL query describes the result set, rather than the specific algorithms used to create it. Database engines support many different algorithms, so even a simple query can have multiple implementation choices, as complicated as out-of-memory parallel algorithms or as simple as just scanning all the rows in the table as if it were a file.
From a performance perspective, indexes are the most important component of relational databases. Indexes do not change SQL queries at all, because the optimizer does the work of figuring out how to use them. For the problems discussed in this book, B-tree indexes are the most appropriate. Other types of indexes exist, such as inverted indexes for text, R-trees for spatial data, and even more esoteric types.
Despite the many implementations of relational databases, there are common themes for writing good queries that perform well. Of course...