Course contentsShow
← Software Engineering
Lesson 880 of 1,93327. Database Design in PracticeFree lesson

Index Selection and Coverage

Understand how query optimizers choose indexes and when covering indexes eliminate the need for table lookups.

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, or ORDER BY clauses 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.