Composite Indexes & the Leftmost Prefix

The inbox that was fine until it wasn't

GET /api/orgs/:orgId/tickets?status=open&assigneeId=…

At fifty rows this is instant. At five million tickets it is the p99 that pages whoever is on call. The ORM is cheerful about it. In Laravel it looks roughly like:

Ticket::query()
    ->where('org_id', $orgId)
    ->where('status', 'open')
    ->when($assigneeId, fn ($q) => $q->where('assignee_id', $assigneeId))
    ->orderByDesc('updated_at')
    ->limit(50)
    ->get();

Which emits something like:

WHERE org_id = ? AND status = ? AND assignee_id = ?
ORDER BY updated_at DESC
LIMIT 50

The usual "fix" is three single-column indexes (org_id, status, assignee_id). Why? Because foreignId()->constrained() indexed the FKs, and a well-meaning migration added the rest. Reads stay mediocre. Every ticket update now maintains three extra B-trees. And status alone is nearly useless: open / pending / resolved is not a needle in a haystack.

This is post three of Learn MySQL. Last time, the primary key was the table's physical layout. Today is secondary indexes. I care most about composite indexes whose column order matches the filters your API actually sends.

Same B-tree, different leaf

A secondary index is another B-tree. You use the same descent you practiced in article two. The leaf holds different data: the indexed columns plus the primary key. InnoDB uses that PK to bounce into the clustered row.[1] Fat PKs make every secondary heavier. Skinny BIGINT PKs keep the luggage light. I'm not rebuilding that demo here. Go poke secondary bounce if you want the two-hop cartoon again.

By the end you should be able to sketch a composite index for a multi-filter list endpoint. You should smell a useless status-only index. And you should catch Eloquent footguns (whereDate, leading %LIKE%, auto-indexed FKs) before they ship. Covering indexes, full EXPLAIN literacy, and keyset pagination show up as short teasers later. I'm not stealing those chapters.

Clustered vs secondary, thirty seconds

The clustered index leaf holds the row. A secondary leaf holds the indexed columns plus the PK columns for that row.[1] A lookup by email, public id, or org+status usually means three steps: walk the secondary tree, read the PK, then walk the clustered tree. Selecting * on a wide tickets row makes that bounce more expensive. Sometimes the index can answer without visiting the row at all (covering indexes, later in the series). Today, design the path into the row.

InnoDB can also silently append PK columns onto a secondary key (index extensions). So an index on (status) is a bit like (status, id) internally.[2] That is neat for WHERE status = ? AND id = ?. Do not design your API indexes around the append. Write the composite index you mean.

What a B-tree secondary is good for

B-tree indexes help when MySQL can walk a sorted range of keys:[3][4]

• Equality: org_id = 42
• IN lists of constants
• Ranges: updated_at > ?, BETWEEN, <
• Prefix LIKE 'refund%' (constant prefix)
• Not: LIKE '%refund%' (leading wildcard, so no range)
• Not: wrapping the column in a function (DATE(updated_at) = ?, LOWER(email) = ?)

If ORDER BY matches a usable left prefix of the index, MySQL can often skip the sort and just read in order.[3] (The direction story gets finer in the pagination post.) You'll see one example below. Keyset pagination gets its own article later in the series.

Leftmost prefix

A composite index (org_id, status, assignee_id, updated_at) is not four indexes duct-taped together. It is one sorted concatenation.[5] MySQL can use a left prefix of that key:

• (org_id)
• (org_id, status)
• (org_id, status, assignee_id)
• (org_id, status, assignee_id, updated_at)

It cannot jump to status or assignee_id while skipping org_id. "Any subset" is the lie that ships bad migrations.

If the rule feels arbitrary, picture a phone book. It's sorted by last name, then first name. Finding every Peckham is easy. Finding "Peckham, Joel" is easy. Finding everyone named Joel means reading the whole book, because first names have no useful order until a last name pins them down. A status filter with no org_id in front is "everyone named Joel."

Equalities (plus IN and prefix LIKE) stack from the left. A range on a middle column uses that column. Then it freezes every column after it for the index range walk. The freeze isn't spite. It's the same sort order doing its job. Within one (org_id, status) pair the entries are sorted by assignee_id. But across a range of statuses those assignee_id values are interleaved runs, not one sorted list. So the walk can't use them to narrow further. MySQL may still filter on the later columns after the fact. But they don't tighten the walk the way people hope.[5] The heuristic for web list queries: equalities first, range last.

Open the nested phone book. Drag the key order and watch the groups re-nest. Tap a column to "know it," put a range in the middle, or try "Missing org_id." Matches shatter into fragments across every org, exactly like hunting every Joel.

These are illustrative rules. They are not a promise the optimizer will never table-scan (stats, LIMIT, buffering). You want the muscle memory.

Optional filters without lying to yourself

Real inbox APIs have optional query-string junk. assigneeId might be absent. q= might appear on Tuesdays. One composite index will not serve every combination perfectly. That is fine.

Design for the common path. If ninety percent of requests are "open tickets for this org, newest first," make (org_id, status, updated_at) the workhorse. That may mean assignee_id is a filter applied after the range. Or you add a second index with a clear job:

• Workhorse: (org_id, status, updated_at) for the default board
• Specialist: (org_id, assignee_id, updated_at) for "my queue" when that page is hot

Two indexes with roles beat five indexes that are vibes. Optional filters are a product decision about which paths deserve write tax. If a filter is rare, accept a weaker plan and move on.

OR across different columns (status = 'open' OR assignee_id = 7) often cannot use one composite index cleanly.[3] Split the query in the app, use UNION, or redesign the UI. Don't invent a fourth skinny index and hope Index Merge saves you. More on that below.

Selectivity: why status alone is a trap

Selectivity is "how many rows share this value?" relative to the query you run. It is not a magic percentage you memorize.[6] On a 5M-row tickets table, status = 'open' might be hundreds of thousands of rows. Inside one org, it might be a few thousand. Same predicate, different haystack.

Old folklore about "indexes only help under 30% of the table" is obsolete. The optimizer decides with estimates and cost, not that rule of thumb.[7] Your job at this stage is the gut check. Low-cardinality flags (status, is_active, booleans) make weak leading keys. High-cardinality lookups (token_hash, email, public_id) make fine single-column uniques. Multi-tenant list endpoints almost always want the tenant id first.

Play Guess Who with the same three questions in two orders. The survivors never change. The peek count does. Only the first question gets a free index jump. Everything after walks whoever is still standing.

Toy math. Production wants realistic volumes in staging. Twenty-row fixtures will lie to you about indexes.

One composite vs many single-column indexes

Back to the inbox. Three singles give MySQL a chance at Index Merge: intersect row ids from each index, then fetch.[8] Sometimes that wins. Often it is a consolation prize. You get extra random lookups, maybe a sort for ORDER BY updated_at, and you still paid write amplification on every update.

One composite index that matches the left prefix of the query is usually the design you wanted. Column order comes from which filters are always present and which are optional. That call belongs to the product shape of the API, not to DBA folklore.

Watch the race: one composite index seeks and finishes, while three singles merge, sort, and bounce, then pay more write tax on every INSERT.

Indexes you do not need still charge rent

Every secondary index is another B-tree to update on INSERT and on UPDATE of indexed columns.[3] Sessions, events, and hot ticket tables feel this first. Space grows too. "Index every column the ORM touched" is how you donate write latency to a future outage.

Before you DROP INDEX in prod, mark it invisible and watch whether anything regresses. The optimizer ignores invisible indexes, but they still get maintained. So you are testing plan choice safely.[9] Adding an index is also ALTER TABLE territory. Online DDL cost is its own article later in the series. Do not promise "just add it on the primary at noon" without that caveat.

After you add indexes, run ANALYZE TABLE if you rely on persistent stats. That's the whole ops sermon for today.[10]

How Eloquent helps you ship bad indexes

Laravel will happily help you shoot your foot:

Schema::create('tickets', function (Blueprint $table) {
    $table->id();
    $table->foreignId('org_id')->constrained()->index();
    $table->foreignId('assignee_id')->nullable()->constrained()->index();
    $table->string('status');
    $table->timestamp('updated_at');

    $table->index('status'); // vibes
});

Part of the trap is that single-column FK indexes appear whether or not you asked. MySQL requires an index on foreign key columns. It creates one during constrained() if nothing suitable exists.[12] So the table above quietly carries a pile of one-column indexes. None of them match the inbox query. Each charges rent on every write.

Better: match the list query / scope you actually run.

$table->index(['org_id', 'status', 'assignee_id', 'updated_at']);

Other classics:

• whereDate('updated_at', $day) becomes DATE(updated_at) = ?. Function on the column, index-unfriendly. Prefer a range on the raw timestamp.
• where('subject', 'like', '%refund%') is a leading wildcard. Fulltext or a search service, not a B-tree hero moment.
• $table->unique('email') / token_hash is good. That is a UNIQUE secondary index for correctness and lookup. It is a different job from the inbox composite index.

Not on Laravel? The lesson survives translation. Whatever your ORM calls the index array, the order you write it in is the left prefix you get. Auto-indexed foreign keys are a starting point, not a strategy.

A few names for later

Covering indexes (later in the series): sometimes every column in the SELECT already lives in the secondary leaf. Then InnoDB skips the clustered bounce.[3] If the index is used but p99 is still sad on wide rows, that is the door. It is not a reason to spam more skinny indexes today.

EXPLAIN (also later): for now, run it on staging data that looks like prod. Look at key / key_len. Full decode when we get there.[11]

EXPLAIN
SELECT id, subject, updated_at
FROM tickets
WHERE org_id = 42 AND status = 'open'
ORDER BY updated_at DESC
LIMIT 50;

Pagination (next up when it lands): ORDER BY updated_at DESC LIMIT 50 wants that column usable in the index left prefix, or you sort after. Descending index details and keyset cursors live there. Matching sort order to the index is why the inbox composite index ends with updated_at.

Worked schemas

Tickets (the inbox)

CREATE TABLE tickets (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  org_id        BIGINT UNSIGNED NOT NULL,
  status        ENUM('open','pending','resolved') NOT NULL,
  assignee_id   BIGINT UNSIGNED NULL,
  subject       VARCHAR(200) NOT NULL,
  updated_at    DATETIME(6) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_org_status_assignee_updated
    (org_id, status, assignee_id, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Uses the index (left prefixes):

SELECT id, subject, assignee_id, updated_at
FROM tickets
WHERE org_id = 42 AND status = 'open'
ORDER BY updated_at DESC
LIMIT 50;

SELECT id, subject, updated_at
FROM tickets
WHERE org_id = 42 AND status = 'open' AND assignee_id = 7
ORDER BY updated_at DESC
LIMIT 50;

Does not use that index for lookup:

-- missing leading org_id
SELECT id FROM tickets WHERE status = 'open' AND assignee_id = 7;

-- OR across non-prefix shapes
SELECT id FROM tickets
WHERE org_id = 42 AND (status = 'open' OR assignee_id = 7);

Orders ("my orders," same pattern, no tenant)

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  created_at   DATETIME(6) NOT NULL,
  total_cents  INT NOT NULL,
  currency     CHAR(3) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_customer_created (customer_id, created_at)
) ENGINE=InnoDB;

SELECT id, created_at, total_cents, currency
FROM orders
WHERE customer_id = 1001
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

Equality then range on the trailing column. Indexing only created_at helps nobody's "my orders" page.

Checklist before you merge the migration

• For every hot WHERE + ORDER BY + LIMIT list endpoint, you wrote down the composite index that matches it before adding three random indexes
• Multi-tenant: org_id / tenant_id leads the index if every query scopes that way
• Optional filters: common path is honest; second indexes have named roles, not vibes
• Eloquent FK auto-indexes were audited against real routes
• Low-cardinality columns are not lone heroes; high-cardinality uniques (email, token_hash) are fine
• You know the write tax on hot tables and used invisible indexes before dropping suspects
• Fat PKs still tax every secondary index (article 02 if you need a refresher)
• You peeked at EXPLAIN key/key_len on prod-shaped data; covering and deep EXPLAIN come later

Secondary indexes are how list endpoints stay honest as tables grow. Next up: WHERE, projection, and sargable queries, and how the columns you ask for interact with the paths you just designed.

References

1. [1]MySQL 9.7 Reference Manual — Clustered and Secondary Indexes
2. [2]MySQL 9.7 Reference Manual — Use of Index Extensions
3. [3]MySQL 9.7 Reference Manual — How MySQL Uses Indexes
4. [4]MySQL 9.7 Reference Manual — Comparison of B-Tree and Hash Indexes
5. [5]MySQL 9.7 Reference Manual — Multiple-Column Indexes
6. [6]MySQL 9.7 Reference Manual — Index Statistics Collection
7. [7]MySQL 9.7 Reference Manual — WHERE Clause Optimization
8. [8]MySQL 9.7 Reference Manual — Index Merge Optimization
9. [9]MySQL 9.7 Reference Manual — Invisible Indexes
10. [10]MySQL 9.7 Reference Manual — CREATE INDEX Statement
11. [11]MySQL 9.7 Reference Manual — Verifying Index Usage
12. [12]MySQL 9.7 Reference Manual — FOREIGN KEY Constraints