CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Spotting a missing index

A full scan on a filtered column, especially a large table, is the signal to check whether an index is missing or unusable.

Spotting a missing index

Think of a shipping quote that unexpectedly comes back as "walk the entire warehouse floor by hand," instead of "look it up directly". Such a quote signals something is clearly wrong with how this delivery is being handled.

In plain words: when an execution plan reports a full table scan on a query that filters a specific column, especially on a large table, that is the concrete signal to check whether an index on that column is missing, unused due to column order as covered on the composite-index page, or simply not written to match how the query actually filters. Comparing the plan before and after adding a candidate index is the direct way to confirm whether that index actually helps this specific query, rather than assuming it will.

A worked example. A full table scan reported for WHERE placed_on = '2026-01-01' on a hundred-million-row orders table, with no index on placed_on, is the exact signal that this column is a strong candidate for a new index.

The key lines.

CREATE INDEX idx_orders_date ON orders (placed_on);
EXPLAIN SELECT * FROM orders WHERE placed_on = '2026-01-01';
-- re-run EXPLAIN after adding the index, to confirm the scan type actually changed

Missing-index signal

A missing-index signal is a full table scan reported in an execution plan for a query filtering a specific column on a large table, indicating that column is a strong candidate for a new or corrected index.

Example: a shipping quote unexpectedly requiring a manual warehouse walk, instead of a direct lookup, is the missing-index signal for that delivery process.

Remember this

Never assume an index is being used; run EXPLAIN and read the scan type directly. This closes the loop on everything this level has covered: an index only helps if the plan actually shows it being chosen.

Quick notes

  • EXPLAIN reports the database's actual chosen execution plan for a query.
  • The scan type field names the access method; full scan or index scan.
  • Estimated rows and cost quantify how expensive the optimizer expects a step to be.
  • A full scan on a filtered, large table is the concrete signal to investigate a missing or unusable index.
  • Comparing plans before and after adding an index confirms whether it actually helped.