Indexes and Why a Query Gets Slow
An index makes lookups fast the same way a table of contents does — including the consequence: adding pages becomes more work.
Without an index, finding one row means checking the entire table from beginning to end — a full table scan. On a thousand rows nobody notices. On ten million, this is what makes a report take minutes.
An index is a separate structure holding column values in sorted order along with pointers to their rows. Exactly like a table of contents: finding chapter seven does not require flipping through the whole book.
Columns worth indexing
- Primary and foreign keys — usually automatic, though foreign keys are not always.
- Columns that appear often in WHERE, particularly on large tables.
- Columns used to JOIN between tables.
- Date columns used to filter a reporting period.
The price you pay
Every index has to be updated on every write. A table with many indexes is slower to INSERT, UPDATE and DELETE, and takes more storage. Indexes are not a setting where more is better — they trade write speed for read speed.
Habits that stop an index being used
-- The index on invoice_date goes unused:
-- the column is wrapped in a function, so its raw value is unrecognisable
WHERE YEAR(invoice_date) = 2026
-- Used: a direct comparison on the column
WHERE invoice_date >= '2026-01-01'
AND invoice_date < '2027-01-01'
-- Unused: a leading wildcard forces a full scan
WHERE customer_name LIKE '%satoria%'Before adding an index, run EXPLAIN (or EXPLAIN ANALYZE) on the slow query. It shows the database's execution plan — whether it really uses an index or scans the whole table. Guessing without reading the plan often ends with an index nothing ever uses.