Understanding SQL Indexes and Query Performance
Contents — Part 2 of 16: What Problem Indexes Actually Solve
- 1.Overview
- 2.What Problem Indexes Actually Solve
- 3.How a B-Tree Index Is Organized
- 4.Clustered vs. Non-Clustered Indexes
- 5.Covering Indexes
- 6.Composite Indexes: Column Order Matters
- 7.SARGability: Why Wrapping a Column in a Function Breaks Index Usage
- 8.Index Selectivity and Cardinality
- 9.How the Query Optimizer Chooses: Seek vs. Scan
- 10.Reading an Execution Plan
- 11.EXISTS vs. IN vs. JOIN for Large Datasets
- 12.Indexes and Sorting: ORDER BY and GROUP BY
- 13.The Write-Performance Cost of Indexes
- 14.When Not to Index
- 15.A Practical Checklist
- 16.Common Mistakes Worth Remembering
What Problem Indexes Actually Solve
Without an index, the only way a database can answer WHERE dept = 'Sales' is to check every single row, one at a time — a full table scan. That's O(n) work regardless of how selective the condition is, and on a table with millions of rows, it's the difference between a query returning instantly and one that takes seconds. An index trades a small amount of extra storage and write overhead for the ability to jump directly to matching rows instead of inspecting every one.
CREATE INDEX idx_employees_dept ON Employees (dept);
This creates a sorted structure of dept values, each pointing back to its corresponding row, so WHERE dept = 'Sales' can jump straight to the matching entries instead of scanning the whole table — the same reason a book's index lets you jump to a page instead of reading cover to cover.