Why this lesson matters

Understand why indexes matter and reason about it with a concrete relational example. Indexes trade additional storage and write work for faster access paths. Their value depends on predicates, selectivity, ordering and the query plan chosen by the database.

How to reason about it

  • For Why Indexes Matter, the outcome to verify is: Run the statement against sample data and inspect the result before moving to the next case.
  • In Why Indexes Matter, keep this failure controlled: Adding an index for every column increases write cost and maintenance without guaranteeing the optimizer will use it.
  • Why Indexes Matter practice target: Compare EXPLAIN output before and after an index on a selective filter, then note how rows scanned and access method change.
Diagram

When an index helps

Practical walkthrough

In the Why Indexes Matter walkthrough: Run the statement against sample data and inspect the result before moving to the next case.

example.sqlsql
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Practice it yourself

Practice

Why Indexes Matter exercise

Compare EXPLAIN output before and after an index on a selective filter, then note how rows scanned and access method change.

  • Record the expected result before execution
  • Test one valid path and one lesson-specific failure path
  • Explain in two lines which boundary owns the decision

Summary