Course contentsShow
← Computer Science
Lesson 1702 of 2,87238. Database Transactions and IndexingFree lesson

Index Selection for Queries

Understanding when the optimizer chooses to use an index versus a full table scan.

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 WHERE clause 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.