Sooner or later every application developer meets the same villain: a query that was instant in development and takes eleven seconds in production. The table grew, and something invisible changed. That invisible something is almost always an index — one that is missing, one that exists but cannot be used, or one the database chose to ignore. Indexes are the highest-leverage performance tool most developers half-understand, and the mental model that fixes that is older than computing: the phone book.
A table without an index is a phone book with the pages shuffled: to find one name, you read every page. That is a full table scan — fine for a hundred rows, catastrophic for ten million. An index is the same book sorted, so you can open near the middle, halve, halve again, and land on the row in a handful of hops however huge the book grows. Sorted lookup is why indexed reads stay fast at any scale.
What an index really costs
If indexes are magic, why not index everything? Because the sorted copy must be maintained. Every insert, update and delete now has to update every index on the table too — each one a little sorted structure demanding its place be found and its pages be shuffled. A table with a dozen indexes turns one write into a dozen. Indexes also occupy real disk and memory, competing with the data itself for cache.
So indexing is a bet: you pay on every write to win on certain reads. The craft is betting only on reads that happen — which is why the honest starting point is not intuition but the database's own query plan. Every serious database will explain how it intends to run a query; the word to fear in that output is the one meaning 'scan', applied to a large table, inside something that runs per-request.
Composite indexes and the leftmost rule
Real queries filter on several columns, which is what composite indexes are for — a phone book sorted by surname, then first name, then street. The order of columns in the index is everything, and it obeys the leftmost rule: an index on (country, city, created_at) accelerates filters on country, on country+city, and on all three — but does nothing for a filter on city alone, exactly as a surname-first phone book is useless for finding everyone named Maria. Most 'I added an index and nothing happened' mysteries are leftmost-rule violations.
The other classic silent killer is wrapping an indexed column in a function or a type conversion inside the filter — asking for everyone whose lowercased surname matches. The sorted order was built on the raw value, so transforming the column throws the sort away and forces a scan. The fixes are mechanical once seen: filter on the raw column, store the searchable form, or create an index on the expression itself.
A working checklist
When a query is slow: get the plan and look for the scan. Check what the filter and the join actually touch — foreign-key columns you join on are the most commonly forgotten indexes in every codebase. Match composite index order to your commonest filters, leftmost first, most selective early. Prefer a few indexes that serve many queries over one per query. And after adding one, read the plan again — the point is not to own indexes, it is to see the scan become a lookup.
Then stop. Deleting unused indexes is as real an optimisation as adding missing ones, because every write pays for each of them forever. The database ships with the instruments to see all of this; the model — a sorted phone book, paid for on every write — is what makes the instruments readable.