Index Selection and Coverage
What you'll learn: How the database query optimizer picks which indexes to use and when "covering" indexes can answer queries without touching the table at all.
How Query Optimizers Choose Indexes
When you run a query, the database's query optimizer evaluates all available indexes and decides which one (if any) will yield the fastest execution. It considers:
- Selectivity: How much the index narrows down rows (from Index Selectivity and Cardinality)
- Column match: Whether the query's
WHERE,JOIN, orORDER BYclauses align with indexed columns - Cost estimates: I/O operations, memory usage, and CPU processing time
The optimizer may choose a single index, combine multiple indexes, or perform a full table scan if no index helps enough.
What "Coverage" Means
An index covers a query when it contains all the columns the query needs—both in the WHERE clause and the SELECT list. This eliminates the need to look up the actual table rows (a "table lookup" or "bookmark lookup").
Example (Conceptual)
-- Table: users (id, email, age, name)
-- Index: idx_email_age ON (email, age)
SELECT email, age
FROM users
WHERE email = 'alice@example.com';
Because idx_email_age contains both email and age, the optimizer can answer this query entirely from the index—no table access needed. This is a covering index for this query.
If you'd asked for name too, the index wouldn't cover it, forcing a table lookup.
Why This Matters
Covering indexes dramatically reduce I/O because they avoid reading full table pages. Query execution plans (from lesson 879) will show this as "Index Only Scan" or similar.
Key Takeaway:
Query optimizers select indexes based on selectivity and cost estimates; a covering index contains all columns needed by a query, eliminating expensive table lookups and maximizing performance.