Database Indexes Explained:The Phone Book for Your App

Database indexes, explained simply: each one works exactly like a phone book: instead of reading every page to find one name, you jump straight to it. Without an index, your database reads every single row of a table to answer a question. With one, a real query on the same data dropped from 2,300 milliseconds to 2.3 — a thousand times faster — because it scanned 47 matching rows instead of 500,000.

Indexes are the highest-leverage performance tool you will ever touch, they require zero code changes, and AI routinely forgets to add them. Here is what they are, the one rule that covers 90% of cases, and the real production numbers that show what is at stake.

Database indexes explained: what is a database index?

Think about how you find “Gonçalves” in a phone book. You do not start at page one and read every entry — you exploit the fact that the book is sorted, jump near the G's, and land on the name in seconds. Now imagine the same phone book with the entries in random order. Finding one name means reading the entire book, every time, for every lookup.

That second phone book is a database table without an index. Tables store rows in no useful order, so a question like “show me this customer's tasks” forces the database to examine every row and keep the matches — a full scan. An index is the sorted phone book the database maintains on the side: a fast-lookup copy of one column (or several) that lets it jump straight to the matching rows.

The user experience translation: 2.3 seconds is a page your customers watch load. 2.3 milliseconds is instant. Same query, same data — the only difference is one index.

The rule: index every foreign key

You do not need to become a performance engineer. One rule covers the overwhelming majority of real-world cases:

Index every foreign key, and every column you filter or sort by frequently.

A foreign key is any column that points at another table — the customer a task belongs to, the project an invoice bills, the status a record carries. In a well-structured database (the kind you get from normalizing properly), these reference columns are everywhere, and they are precisely what the database uses to connect tables when answering questions. Every one of them gets an index. No exceptions, no judgment call.

Beyond that, index the columns your screens constantly filter and sort by: the organization each row belongs to (the backbone of multi-tenant apps, filtered on virtually every query), creation dates for “newest first” lists, and the soft-delete timestamp that nearly every query checks to hide deleted records. The habit is one line per column at table-creation time — CREATE INDEX idx_tasks_project_id ON tasks(project_id); — and it is a habit AI will not maintain for you: generated schemas routinely define the references and skip the indexes, which works perfectly in a demo with 50 rows and collapses with 500,000.

The bookThe Solopreneur RevolutionLuis Gonçalves on building a solopreneur business with AI while employed.Get the book →

Composite indexes: the phone book's second trick

A phone book is not sorted by one thing — it is sorted by last name, then first name. That lets you find every “Smith,” or exactly “Smith, John.” What it cannot do is find every “John” regardless of last name — for that, you are back to reading the whole book.

A composite index is the same structure over two or more columns, and it obeys the same left-to-right law. An index on (organization, status) accelerates “everything for this organization” and “everything for this organization with this status” — but does nothing for “everything with this status across all organizations,” because status is the first name, not the last.

The practical consequence: column order matters. Put the column you always filter by first — in a multi-tenant product, that is the organization. And composite indexes have a bonus power: if the index's order matches your query's sort order, results come out pre-sorted, skipping the sorting step entirely.

When NOT to index

Indexes are not free: each one consumes disk space and must be updated on every write, so every additional index makes inserts and updates a little slower. Four situations where you should skip them:

Two-value columns. A true/false flag splits the table roughly in half — jumping to “half the book” saves nothing. Small tables. Under about 1,000 rows, the whole table is effectively instant to read; the primary key is enough. Columns you never search. Only columns that appear in filters, sorts, or connections earn an index — free-text notes do not. Constantly-changing columns on write-heavy tables. Every change must also update the index; on a table that is mostly written and rarely read, that trade goes the wrong way.

The golden rule: every index should justify its existence with a measurable query improvement. “Just in case” indexing is how you end up with slow writes and wasted disk.

Real production numbers

Three real before-and-after measurements, each a common screen in a SaaS product:

Every row is the same story: the database stopped reading everything and started jumping. And the workflow that produced them is repeatable by any founder: find the slow screen, ask the database to explain how it answered the query (every database has an “explain” feature — and asking AI to interpret its output works beautifully), add the index the explanation points to, measure again. When the report says “scanned 500,000 rows to return 20,” you have found your missing index.

12 weeksYour corporate career was never the planApply to the Solopreneur Accelerator.Apply now →

Frequently asked questions

How do I find the indexes I'm missing?

Follow the slowness. When a page drags, take its query, run your database's explain feature on it, and look for full table scans — the report literally says how many rows were read versus returned. Modern database dashboards also surface the slowest queries and often suggest the missing index outright. Paste any of it into AI for a plain-English interpretation.

Can I have too many indexes?

Yes. Each index taxes every write and occupies disk, so a table with a dozen speculative indexes pays real costs for hypothetical queries. Start with the rule — foreign keys, organization, common filters — then add more only when a measured slow query demands it, and prune indexes that nothing uses.

Do indexes speed up writes too?

No — the opposite, slightly. An index accelerates reading but must itself be updated whenever data changes, so writes pay a small tax per index. For a typical SaaS product, which reads far more than it writes, the trade is overwhelmingly worth it; just do not index columns nothing ever searches.

Will AI add indexes for me automatically?

Sometimes, inconsistently — and forgetting foreign-key indexes is one of the most common gaps in AI-generated schemas, because the demo works fine without them. Make it explicit: tell AI that every table it creates must index its foreign keys and commonly filtered columns, and review the schema against that rule like a checklist.