Index Selection for Queries
What you'll learn: You'll discover how database query optimizers decide whether to use an index or perform a full table scan when executing your queries.
The Optimizer's Choice
When you run a query, the database doesn't blindly use every available index. Instead, the query optimizer estimates the cost of different execution strategies and chooses the most efficient one. Sometimes that means using an index; other times it means scanning the entire table.
Think of it like choosing between a book's index and flipping through every page. If you're looking for one specific mention of "Byzantine Empire," the index wins. But if you need to read 80% of the book anyway, starting at page 1 is faster than constantly jumping around.
When the Optimizer Chooses an Index
The optimizer favors index usage when:
- High selectivity: Your
WHEREclause filters out most rows (e.g., finding one customer ID among millions) - Indexed columns match query predicates: The query filters or sorts on indexed columns
- Low cardinality estimation: The optimizer estimates few rows will match
- Index covers the query: All needed columns exist in the index itself
When Full Table Scan Wins
The optimizer skips indexes when:
- Low selectivity: Your query returns a large percentage of rows anyway
- Small tables: Reading the whole table is faster than index overhead
- Missing or outdated statistics: The optimizer misjudges cardinality
- Complex predicates: Functions or expressions on indexed columns (e.g.,
WHERE UPPER(name) = 'ALICE') prevent index use
The optimizer uses cost-based optimization with cardinality estimation (from previous lessons) to calculate the expected I/O operations for each approach, then picks the cheapest.
Key Takeaway: The query optimizer chooses indexes based on estimated cost, favoring them for highly selective queries but opting for full table scans when retrieving large portions of data or when index overhead outweighs benefits.