First, the key terms.

Query plan
The database’s selected strategy for executing a query.
Selectivity
How much of the data a query condition is expected to match.

The idea, explained

Indexes trade storage and write overhead for faster access patterns. A B-tree index is useful for many equality and range queries, while other index types support different operations. A composite index’s column order affects which queries it can help. The query planner decides whether using an index is worthwhile based on available information. Inspect representative query plans rather than assuming every index will be used.

STEP 1

Match the access pattern

An index on email can locate a matching subscriber efficiently. A query that asks for nearly every row may benefit less. Composite indexes support particular column combinations and orderings; listing every column in an index is not automatically helpful.

STEP 2

Measure reads and writes

Each inserted or updated row may require index maintenance. Use representative data and inspect query plans. A development table with ten rows does not reveal production behavior on millions of rows.

Follow a worked example.

A newsletter database looks up a subscriber by email. An index on email can avoid scanning thousands of rows. Adding an index to every column would also increase the work required for each insertion and update.

Walk it through, one step at a time

  1. A lookup searches for one email among 100,000 subscribers. An email index can narrow the search substantially.
  2. A bulk export requests all subscribers. Reading most of the table may be reasonable even with that index present.
  3. Compare actual plans and timings before adding another index, including the impact on signup writes.

Where people get stuck

An index does not automatically accelerate queries that transform or filter data in incompatible ways.

Now try it yourself.

Pause before opening each answer. Explain your reasoning in your own words.

1. Check the core idea

Why can a database choose a table scan even when an index exists?

Reveal the answer & reasoning

Reading much of a small table or a large fraction of matching rows can cost less than following the index and fetching rows individually.

2. Apply what you learned

Why should unused indexes be reviewed?

Reveal the answer & reasoning

They can consume storage and add write overhead without improving relevant queries. Index value depends on the workload, not merely its existence.