Database Indexes in Practice: Clustered vs Secondary, Composite, Covering, and Why Your Index Is Ignored

One composite index can turn a query that reads a whole table into one that reads a handful of pages. Ten careless indexes can make every insert in your app quietly slower, eat memory you needed for hot data, and still not help the query that is actually slow. This blog is about telling those two situations apart, and about the reasons a perfectly good index gets ignored.

For this blog, we'll stay on one machine and one table. We'll use an invented food-delivery app called Tiffin and its busiest table:

-- PostgreSQL syntax; MySQL is nearly identical
CREATE TABLE orders (
  id            bigint PRIMARY KEY,
  user_id       bigint      NOT NULL,
  restaurant_id bigint      NOT NULL,
  status        text        NOT NULL,  -- placed, preparing, out_for_delivery, delivered, cancelled
  created_at    timestamptz NOT NULL,
  total         numeric(10,2) NOT NULL
);

Three screens hit it all day: a customer's "My orders" page, a restaurant's kitchen screen of live orders, and a finance report over date ranges. We'll come back to all three at the end and design indexes for them. Sharding this table across machines is a different story, told in Sharding.

01What an index is, in one picture

Think of the index at the back of a cookbook. If you want every recipe that uses paneer, you don't read 400 pages. You flip to "P", find "paneer: 34, 112, 287", and go to those pages. The PostgreSQL docs use the same analogy, and add the part people forget: it's the author's job to guess which terms readers will look up. In a database, that author is you.

Almost every index you'll create is a B+tree: a shallow tree of fixed-size pages where the leaves hold the sorted keys and are linked left to right, so a range read is "find the start, then walk sideways". I won't re-derive it here; Storage data structures walks through it properly. Two numbers are worth carrying around:

• Page size. PostgreSQL stores tables and indexes in pages that are usually 8 kB. InnoDB's B+tree nodes are 16 KB by default. The database reads whole pages, never single rows.
• Fanout. PlanetScale's back-of-envelope maths: with 16 KB nodes and 8-byte keys, values and pointers, one node holds about 682 entries, so a three-level tree can address over 317 million of them. That's why a lookup in a huge table still touches only three or four pages.

Hold on to that last point. An index lookup is cheap because it reads few pages; everything that makes it read more pages makes it slower. Most of this blog is that sentence applied over and over.

02Clustered vs secondary: where does the row actually live?

Here's the question that separates "I've used indexes" from "I know how they work": when the index finds your key, where is the rest of the row? MySQL InnoDB and PostgreSQL give opposite answers.

MySQL InnoDB: the table is the primary key tree

InnoDB stores all table data in a B+tree keyed by the primary key, with the other columns in the leaves. A secondary index is another B+tree whose values are the primary key, not a disk address. So WHERE user_id = 42 walks the user_id index to get some order IDs, then walks the primary key tree once per ID to fetch each row.

Why store a key instead of an address? Because rows in a clustered tree move whenever pages split. A logical key survives that; a physical address wouldn't. Use The Index, Luke puts the trade-off bluntly: reads through a secondary index on a clustered table pay for two tree walks, and the clustering key copied into every secondary entry makes those indexes bigger.

PostgreSQL: a heap plus pointers

Postgres keeps rows in an unordered heap. In the docs' own words, all indexes in PostgreSQL are secondary indexes, including the primary key's. Each entry points at a tuple location called a TID (or ctid): a page number plus a slot on that page. One hop, no second tree.

The diagram below shows the same lookup on both engines.

So where does Postgres pay? On updates. Postgres uses MVCC, keeping old row versions around for readers that still need them, so an update writes a new row version at a new TID, and indexes point at TIDs. Uber's Postgres to MySQL write-up (written against Postgres 9.2) made this famous: on a table with a dozen indexes, an update to a column covered by just one index still had to be propagated into all 12, and every one of those writes also went into the WAL and over the replication link.

Postgres has a mitigation called HOT (heap-only tuples). If an update changes no indexed column and the new version fits on the same page, no new index entries are needed. That gives you a practical rule: every column you add to an index is a column whose updates can no longer be HOT.

03Choosing a primary key: sequential, random or time-ordered

On InnoDB the primary key decides the physical order of the whole table. On Postgres it's still a B-tree that receives every insert. Either way, the shape of your keys matters. You have three realistic choices.

The next figure shows why the insert pattern matters more than the size.

Random keys hurt in three ways:

• Every page is hot. PlanetScale's B-tree visualisation shows random inserts visiting unpredictable paths, while sequential inserts revisit the same right-most path. Once the index is bigger than your buffer pool, random inserts start turning into disk reads.
• Pages stay half empty. InnoDB fills pages to about 94% when keys arrive in order, and as low as 50% when they're random. Same rows, up to roughly twice the pages (our arithmetic).
• Bigger keys everywhere. A UUID is 16 bytes against a BIGINT's 8, and in InnoDB every secondary index entry carries a copy of the primary key. Store it as CHAR(36) and it's 288 bits per value.

Time-ordered IDs keep the "generate anywhere" property and fix the insert pattern. UUIDv7 puts a Unix timestamp in its leading bits, and PostgreSQL 18 ships a built-in uuidv7() function. Instagram's Snowflake-style IDs pack 41 bits of milliseconds, 13 bits of shard ID and 10 bits of sequence into a 64-bit integer, so they sort by time and fit in a bigint.

04Composite indexes: column order is the whole game

A phone directory is the classic picture: sorted by surname, then first name. Finding "Sharma, Priya" is instant. Finding every Priya means reading the whole book. Use The Index, Luke builds its whole chapter on this, and it gives us the first rule.

Rule 1: the leftmost prefix

An index on (restaurant_id, status, created_at) can seek on restaurant_id, on restaurant_id + status, or on all three. A query that filters only on status can't seek into it, because matching entries are scattered under every restaurant. PlanetScale's MySQL write-up shows the index not even appearing in possible_keys for that case.

One modern footnote: PostgreSQL 18 added B-tree skip scan, which can use a later column when the leading column has very few distinct values. It's a nice fallback, not a design strategy. Design for the leftmost prefix.

Rule 2: equality first, then range

The Postgres docs state the exact rule: equality conditions on the leading columns, plus an inequality on the first column without an equality, limit how much of the index is scanned. Anything to the right of the range column is only checked while scanning.

Take the kitchen screen: WHERE restaurant_id = 7 AND created_at > now() - interval '2 hours'. With (restaurant_id, created_at), the database seeks to restaurant 7 at the two-hour mark and reads forward. With (created_at, restaurant_id), it reads every restaurant's orders from the last two hours and throws most away. Use The Index, Luke uses this exact shape to bust the myth that the most selective column should always go first. What matters is which columns use = and which use a range.

Rule 3: let the index do the sort

A B-tree already holds entries in order. If the index order matches your ORDER BY after the equality columns, the database reads rows in order and stops early. The Postgres docs call out ORDER BY ... LIMIT n as the important case: without a matching index the database must process every matching row to find the top n; with one, it reads n entries and stops. Use The Index, Luke calls this a pipelined top-N query: its response time grows with the rows you ask for, not with the table.

Here is the kitchen query against two different column orders.

Not fully. The index gives you two sorted runs, one per status, and the database has to merge or sort them. For two values that's cheap. It's also a hint that a partial index may fit better, which we'll get to below. And the docs add a sanity check worth quoting in interviews: indexes with more than three columns are unlikely to help unless your queries are very stylised.

05Covering indexes and index-only scans

Even a perfect seek still has to fetch each row to read columns the index doesn't have. For "My orders" that's 20 random page reads for 20 rows. A covering index contains every column the query needs, so the database never visits the table. The plan operation is an index-only scan; Use The Index, Luke points out that "covering" sounds like a property of the index, when it really describes how one query runs against it.

-- PostgreSQL: key columns for the seek and sort, INCLUDE for the payload
CREATE INDEX orders_user_recent
  ON orders (user_id, created_at DESC)
  INCLUDE (status, total);

SELECT id, created_at, status, total   -- id is not in the index...
FROM orders WHERE user_id = 42
ORDER BY created_at DESC LIMIT 20;     -- ...so this one still visits the heap

Spot the trap? In Postgres the index doesn't contain id, so that query isn't covered. Either drop id from the select list or add it to INCLUDE. InnoDB is kinder here: every secondary entry already carries the primary key, so id comes free. In MySQL you'd write (user_id, created_at, status, total) and look for Using index in the Extra column of EXPLAIN.

The flow below adds the Postgres twist.

Why the check? Postgres stores visibility information only in the heap, not in index entries. So an index-only scan checks the visibility map, a bitmap with one bit per heap page that says "everything here is visible to everyone". The map is four orders of magnitude smaller than the heap, so checking it is cheap. But if the bit isn't set, the heap visit happens anyway. The docs' conclusion: INCLUDE columns only pay off on tables that change slowly enough for most pages to be all-visible. An orders table where yesterday's rows are frozen and today's are churning is a mixed case: the old history is covered, the live orders aren't.

06Why your index is ignored

You created the index. EXPLAIN still says Seq Scan (Postgres) or type: ALL (MySQL). There are two families of reasons, and telling them apart is most of the fix.

Family 1: the query hides the column

• A function on the column. WHERE DATE(created_at) = '2026-10-20' asks about DATE(created_at), which isn't what the index stores. PlanetScale calls this index obfuscation. Fix: move the work to the other side, created_at >= '2026-10-20' AND created_at < '2026-10-21'. If you truly need the function, index the expression (below).
• An implicit type cast. Store phone numbers in a text column, then query WHERE phone = 9845012345 with no quotes. Many databases silently compare as numbers by converting the column, which is a function on the column again. Use The Index, Luke notes PostgreSQL raises an error here instead. Fix: send the right type.
• A leading wildcard. A B-tree can serve LIKE 'pan%' but not LIKE '%paneer', because sorted order only helps from the left. In a non-C locale Postgres also needs a special operator class even for the prefix case. For "contains" searches, use a pg_trgm GIN index or real full-text search.
• A skipped leading column. The leftmost-prefix rule from above.
• OR across columns. One index scan can only use conditions joined by AND, so user_id = 42 OR restaurant_id = 7 can't use one composite index. Postgres can combine two separate indexes with a bitmap OR; MySQL has a similar index merge. Often the cleanest fix is a UNION of two indexed queries.

Family 2: the planner thinks a scan is cheaper

• The predicate matches too many rows. An index on status is useless for status = 'delivered' if most orders are delivered. Each index hit is a random page read; reading the table in order is faster once you need a big fraction of it. PlanetScale's piece walks through selectivity and why a table scan is sometimes the right answer. Note that it's query-dependent: the same index is great for the rare status = 'cancelled'.
• Stale or naive statistics. The planner decides from stored statistics, not your data. InnoDB refreshes them automatically after about 10% of a table changes (per the PlanetScale piece above); Postgres relies on ANALYZE (usually via autovacuum). When columns are correlated, like restaurant_id and city, Postgres assumes they're independent and underestimates; extended statistics fix that.
• The table is small. Ten pages are faster to scan than to index. Your dev database with 200 orders will always scan. Test on realistic volumes.

A tiny before and after

Here's the "My orders" query on Postgres before and after adding orders_user_recent. This output is illustrative and trimmed, not from a real run, so I've left the costs and timings out.

-- Before: only the primary key exists
Limit
  ->  Sort   (Sort Key: created_at DESC)
        ->  Seq Scan on orders
              Filter: (user_id = 42)

-- After: CREATE INDEX orders_user_recent ...
Limit
  ->  Index Only Scan using orders_user_recent on orders
        Index Cond: (user_id = 42)
        Heap Fetches: (low if pages are all-visible)

Read it bottom-up. Before: read every row, keep this user's, sort them all, keep 20. After: seek, read 20 entries in order, stop. In MySQL the same change shows up as type moving from ALL to ref and Using filesort disappearing from Extra.

Real world: the plan that flipped overnight

PlanetScale described a customer incident in "When the Postgres query planner goes rogue". No deploy, no traffic change, yet database CPU jumped from 20% to 100%. One query pattern, almost 50% of total runtime on the cluster, went from milliseconds to about 10 seconds, the configured timeout. The cause: as data grew, the statistics shifted and the planner abandoned an index for full table scans. The fix path is the one I'd describe in an interview: contain the damage, compare old and new plans with EXPLAIN (ANALYZE, BUFFERS), then refresh statistics, adjust the index or rewrite the query.

07What every index costs

Indexes are not free reads. They're reads you prepay on every write. The figure shows one Tiffin checkout.

• Slower writes. Every insert, delete and non-HOT update touches every index. The author of PlanetScale's piece on index downsides reported a bulk insert of about a million rows taking 10-15 seconds without indexes and closer to two minutes after adding several for slow queries. They accepted it because the job ran off-peak. Your checkout path might not have that luxury.
• Storage and memory. Each index is another tree that competes for the same buffer pool as your hot rows. Wide INCLUDE columns and long primary keys (in InnoDB) make this worse.
• Lost HOT updates. In Postgres, indexes can prevent heap-only tuples, and the docs' advice is plain: remove indexes that are seldom or never used.
• Build cost. A plain CREATE INDEX in Postgres blocks writes until it finishes. CREATE INDEX CONCURRENTLY avoids that at the price of a slower build with caveats.
• Unique checks. A unique index must check for an existing key before every insert. That's exactly what you want for an idempotency key on checkout: a retried request can't create a second order, and the check is atomic, unlike an application-level "SELECT then INSERT" that two requests can race through.

Not everyone loves unique indexes at scale. A ByteDance engineer argues on HackerNoon that adding a composite unique index to a table with tens of millions of rows dragged on for days with replication lag swinging around, and that business-level duplicates (like differently-cased emails) need application logic anyway. Both points are fair. My default is still: put real invariants like idempotency keys in a unique index, normalise the value before it gets there (or index lower(email)), and plan big index changes as migrations, not one-liners.

To test a removal safely, MySQL has invisible indexes: the optimizer stops using the index but it's still maintained, so you can flip it back if a query regresses.

08Beyond plain B-trees, briefly

Postgres ships several index types. You don't need them all for interviews, but these five come up.

• Partial indexes are the quiet hero. The docs point out that a common value matching more than a few percent of rows won't use an index anyway, so leaving those rows out makes the index smaller and spares many updates from touching it. The catch: the query's WHERE must visibly imply the index predicate, and a parameterised status = $1 won't match.
• Expression indexes are the escape hatch for "function on the column". They're computed on every insert and non-HOT update, so use them where reads matter more than writes.
• GIN is an inverted index of (key, posting list) pairs: the same structure search engines use, explained in Storage data structures. It powers JSONB containment (@>), arrays and full-text search, but can't do index-only scans.
• BRIN stores a summary per range of pages, so it's tiny. It works well only when the column tracks physical order, which is exactly what created_at on an append-only orders table does.

09How to say it in the interview

When I ask "this query is slow, what do you do?", I'm not listening for "add an index". I'm listening for a loop: find it, read the plan, explain the mismatch, fix the cheapest thing, verify. Here's the loop as a picture.

Designing for Tiffin's three access patterns

Start from queries, not columns. Here's what I'd put on the whiteboard, assuming PostgreSQL.

-- 1. "My orders": latest 20 for a customer
CREATE INDEX orders_user_recent ON orders (user_id, created_at DESC)
  INCLUDE (status, total);

-- 2. Kitchen screen: live orders for one restaurant, oldest first
CREATE INDEX orders_kitchen_live ON orders (restaurant_id, created_at)
  WHERE status IN ('placed', 'preparing');

-- 3. Finance: orders in a date range, append-only table
CREATE INDEX orders_created_brin ON orders USING brin (created_at);

• Pattern 1 is a textbook equality-then-sort with LIMIT. It also supports keyset pagination: "older than the last created_at I showed".
• Pattern 2 uses a partial index because live orders are a sliver of the table. The kitchen query must repeat the same status IN (...) literally. On MySQL I'd use the plain composite (restaurant_id, status, created_at).
• Pattern 3 uses BRIN because the finance report reads big date ranges and rows arrive in time order. If the report needs per-restaurant filters too, a B-tree on (restaurant_id, created_at) may win; I'd test both.

Plus the unique index on idempotency_key from checkout. That's four secondary indexes for a write-heavy table, each mapped to a screen or an invariant. Notice what's missing: no index on status alone, no index on total.