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
EXPLAINreports 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.