2011-11-08The Where Clause

The previous chapter explored the index structure and discussed the cause of poor index performance. In the next step, we learn how to spot these causes in SQL statements. First, looking at the where clause.

The where clause defines the search condition of an SQL statement, and thus falls into the core domain of an index: finding data quickly. Although the where clause has a huge impact on performance, it is often phrased carelessly so that the database has to scan a large part of the index. That means, a badly written where clause adds the first ingredient to a slow query.

This chapter explains how different operators affect index usage and how to make sure that an index is usable for many queries. The last section presents common anti-patterns and alternatives that deliver better performance.