Understanding SQL Indexes and Query Performance

Part 15 of 16 · ~2 min

A Practical Checklist

• Index columns that appear in WHERE, JOIN, and ORDER BY clauses for queries that actually run often — start from the workload, not from the schema.
• Order composite index columns as: equality predicates first, then the one range or ORDER BY column, then any columns present only to cover the SELECT list. Use selectivity to break ties among the equality columns.
• Put a column that some queries filter on alone in the leftmost position, whatever its selectivity — an index is unreachable to a query that cannot use its leading column.
• Keep indexed columns raw in WHERE clauses. If you must transform, rewrite to a range on the raw column; if the transformation is permanent and frequent, build a functional index for that exact expression.
• When you move arithmetic from the column to the constant, compute the constant exactly and write it as a literal. A division left in the query gets evaluated in binary floating point, which can land a hair off the boundary you meant and change which rows come back.
• Reserve covering indexes for the handful of queries that are both genuinely hot and latency-sensitive — remember that covering is a property of the query, not the index, so adding one column to the SELECT list can silently uncover it again.
• Prefer EXISTS over IN, and especially over NOT IN, whenever the subquery's column is nullable or not guaranteed unique.
• Confirm every assumption with an actual execution plan. Every rule in this reference is a prediction; the plan is the answer.
• Check for stale statistics before adding an index to fix a regression — ANALYZE is cheaper than a new B-tree and fixes a surprising share of "it used to be fast."
• Audit for unused and redundant indexes periodically. An index whose leftmost column is the leading column of another composite index is usually deletable outright.