CodeOath
← All posts
SQL65 min total · 16 parts

Understanding SQL Indexes and Query Performance

Contents — Part 15 of 16: A Practical Checklist
  1. 1.Overview
  2. 2.What Problem Indexes Actually Solve
  3. 3.How a B-Tree Index Is Organized
  4. 4.Clustered vs. Non-Clustered Indexes
  5. 5.Covering Indexes
  6. 6.Composite Indexes: Column Order Matters
  7. 7.SARGability: Why Wrapping a Column in a Function Breaks Index Usage
  8. 8.Index Selectivity and Cardinality
  9. 9.How the Query Optimizer Chooses: Seek vs. Scan
  10. 10.Reading an Execution Plan
  11. 11.EXISTS vs. IN vs. JOIN for Large Datasets
  12. 12.Indexes and Sorting: ORDER BY and GROUP BY
  13. 13.The Write-Performance Cost of Indexes
  14. 14.When Not to Index
  15. 15.A Practical Checklist
  16. 16.Common Mistakes Worth Remembering
Part 15 of 16 · ~1 min

A Practical Checklist

  • Index columns that appear often in WHERE, JOIN, and ORDER BY clauses — not every column, and not defensively.
  • Put the most selective / most commonly filtered-alone column first in a composite index, unless queries always filter on a specific column together with others.
  • Avoid wrapping indexed columns in functions inside WHERE clauses; if you genuinely need to, consider a functional/expression index instead.
  • Consider a covering index for a small number of genuinely hot, latency-sensitive queries — not as a default for every query.
  • Prefer EXISTS over NOT IN for subqueries whose data could contain a NULL.
  • Confirm any performance assumption with an actual execution plan (EXPLAIN) rather than reasoning from the SQL text alone — real statistics on real data settle every debate in this reference.
  • Periodically check for unused indexes — most engines expose this — since they cost write performance for no read benefit.
← Previous
When Not to Index
Next →
Common Mistakes Worth Remembering