SQL Indexes

Local review for this concept

Start a focused review session for SQL Indexes.

Learning objectives

• Choose indexes that match filters and ordering.
• Apply the leftmost prefix rule for composite indexes.
• Recognize when an index is low-value or creates unnecessary write overhead.
• Apply expand-contract thinking to columns, renames, indexes, and constraints.
• Plan backfills that avoid unnecessary locks and production load spikes.
• Explain why schema changes must be compatible with mixed application versions.

Common mistakes to avoid

• Indexing a low-cardinality boolean column by itself and expecting a large speedup.
• Creating composite indexes in the wrong column order.
• Ignoring ORDER BY and then paying for a separate sort.
• Renaming or dropping a column while old app versions still use it.
• Running huge backfills or index builds in the most blocking mode during peak traffic.
• Adding constraints before all writers and old rows satisfy them.