Understanding SQL Indexes and Query Performance

Part 16 of 16 · ~2 min

Common Mistakes Worth Remembering

Every one of these is something the pay-band screen did wrong at some point in this reference.

• Assuming an index gets used because it exists. Chapter 1 indexed dept and chapter 7 showed the optimizer ignoring it for 'Sales' and using it for 'Facilities'. Index usage is a cost decision made per query, per literal, against current statistics.
• Picking the right columns for a composite index in the wrong order. Chapter 4 built (dept, name, salary) and chapter 5 proved it could not seek the salary range or supply the sort, because name sat between the equality column and the range column. Reordering to (dept, salary, name) — the same three columns — is what made the screen fast.
• Expecting a composite index to help queries on any of its columns. Only a leftmost prefix is seekable. In chapter 5, before any standalone salary index existed, WHERE salary > 60000 planned as a full scan with (dept, salary, name) sitting right there.
• Putting a function or arithmetic around an indexed column inside WHERE. WHERE salary / 10000 = 6 scans; WHERE salary >= 60000 AND salary < 70000 seeks. Same rows.
• Assuming an algebraic rewrite preserves the result set. salary * 1.1 > 66000 and salary > 66000 / 1.1 disagree about anyone earning exactly 60000, because binary floating point cannot represent the boundary exactly.
• Adding an index without reading a plan, so you never learn whether the index was used, or whether indexing was the bottleneck at all.
• Treating indexes as free. Five indexes on Employees turned 431,000 salary updates into roughly 1.7 million index modifications. Two of those five were redundant and cost that for nothing.
• Blaming the schema for stale statistics. A query that degrades with no code change is far more often an ANALYZE away from fixed than an index away.

For a closer look at joins, WHERE, and aggregates working together, there's SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries — or head to the code lab and build the pay-band screen's query yourself; every statement in this reference runs there unchanged.