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.
The B-tree structure underneath the phone-book analogy
Most relational database indexes are implemented as a B-tree, a balanced tree structure where every leaf sits at the same depth, and looking up a value means descending from the root through a small, predictable number of intermediate nodes to reach the leaf holding the actual data pointer — the phone-book analogy captures the intuition of sorted, jump-to-the-right-section lookup well, but the actual B-tree structure is what guarantees that lookup time grows only logarithmically with table size, staying fast even as a table grows from thousands to billions of rows.
Composite indexes: why column order in the index definition is not arbitrary
An index defined across several columns together is only efficiently usable for a query filtering on a prefix of those columns in the same order they were declared — an index on `(country, city)` speeds up a query filtering by country alone or by country and city together, but does nothing to speed up a query filtering by city alone, since the index's own sorted order groups entries by country first and city only within each country group — this ordering rule is precisely why the column order chosen when defining a composite index has to match the actual, real query patterns it is meant to serve, not simply reflect whatever order feels most natural to write down.
Why every index makes writes slower, not just reads faster
An index is not a free performance improvement; every insert, update, or delete on an indexed table also has to update every index defined on that table to keep it consistent with the underlying data, which means a table with many indexes pays a real, cumulative write-performance cost for each one — this is exactly why indexing is a genuine trade-off between read and write performance, not a strictly positive change, and adding an index to a heavily-written table deserves the same deliberate justification as any other performance trade-off.
Covering indexes: when the index itself can answer a query without touching the table at all
A covering index includes every single column a specific query actually needs, which lets the database satisfy that query by reading only the index itself, never needing to follow the index's pointer back to the full table row at all — this is a meaningfully faster path than an ordinary index lookup followed by a separate row fetch, and deliberately designing an index to cover a specific, frequent, performance-critical query is a genuine, advanced optimization technique worth knowing exists once the basic mental model in this article is solid.
Why an index on a low-cardinality column often provides little real benefit
An index on a column with very few distinct values, like a boolean flag, provides limited benefit even though it is technically usable, since a query filtering on it still has to retrieve a large fraction of the table's rows either way, and the database's own query planner frequently decides a full table scan is actually cheaper than following an index that would not meaningfully narrow the result down — this is exactly why indexing decisions should weigh not just whether a column is queried, but how selectively it actually narrows down the result set.
Why an index needs periodic maintenance, not just initial creation
A B-tree index can become fragmented over time as rows are inserted, updated, and deleted, degrading its balance and efficiency gradually rather than all at once — which is exactly why production database systems support periodic index maintenance operations like rebuilding or reorganizing, and a team that creates indexes once and never revisits their ongoing health can see query performance quietly degrade over months even though nothing about the schema itself ever explicitly changed.
Why the phone-book analogy has one important limit worth naming explicitly
A phone book is sorted once and stays that way, while a database table is constantly being inserted into, updated, and deleted from, which means the index has to be actively maintained in real time as the underlying data changes, unlike a phone book's fixed, unchanging structure — this is exactly why the write-cost trade-off and the periodic maintenance discussed elsewhere in this article matter, and it is the one place the otherwise-useful phone-book analogy stops fully capturing how a real index actually behaves.
Why understanding this mental model changes how a developer designs a schema, not just how they debug one
Knowing in advance how indexing actually works changes decisions made much earlier than any debugging session — which columns are likely to need an index based on anticipated query patterns, how to order a composite index's columns, whether a given design will scale reasonably as a table grows — which is exactly why this mental model belongs in schema design conversations from the start, not only reached for reactively once a query has already become unacceptably slow in production.
Why this mental model transfers directly to non-relational databases too, despite different terminology
Document databases, key-value stores, and other non-relational systems all implement some conceptual equivalent of an index, using different terminology but solving the identical underlying problem — trading some write cost and storage space for faster, more targeted reads — and the core mental model this article has built, sorted structure enabling fast lookup at a real, ongoing maintenance cost, transfers directly regardless of which specific kind of database happens to be in use.
Why this article's mental model is worth teaching to anyone writing queries, not just database specialists
Understanding indexing well enough to reason about it is not a specialized skill reserved for dedicated database administrators; any developer who writes queries against a real database benefits directly from understanding why a given query is fast or slow, and treating this as core, general engineering knowledge rather than a niche specialty produces teams that write measurably better queries from the start, rather than discovering performance problems only after they have already reached production.
Why this article's closing thought is that indexing decisions are never actually free
Every specific mechanism covered throughout this article — the B-tree structure, composite column ordering, the write-cost trade-off, covering indexes — reinforces the same underlying point: there is no such thing as a free index, only a deliberate, informed trade between read speed, write cost, and storage, and genuinely understanding that trade-off, rather than treating indexing as an unconditional performance win, is what this article's mental model actually delivers.
Why a database migration tool's own generated indexes deserve the same scrutiny as hand-written ones
An ORM or migration tool that automatically creates indexes based on schema annotations can generate indexes that do not actually match real query patterns, or miss composite indexes a hand-crafted schema would have included deliberately — treating auto-generated indexes as a starting point worth reviewing against actual query patterns, rather than assuming the tool's defaults are automatically well-suited to the application's real, specific access patterns, closes a common, easily overlooked gap.
Why this article's mental model is worth revisiting after working with several different database systems
The specific commands and exact tuning knobs differ across PostgreSQL, MySQL, and other relational systems, but the underlying B-tree mechanism and its trade-offs, covered throughout this article, transfer directly across all of them, which is exactly why the effort spent understanding it deeply pays off across an entire career rather than being tied to the specifics of any one particular database product.