Index Types

## What a compound index is `db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })` builds one index whose keys are ordered tuples: first by `userId`, then within equal `userId` by `status`, then within equal `(userId, status)` by `createdAt` descending. There is one index, not three. Everything about which queries it serves follows from that single ordering. ## The prefix rule Because the sort is lexicographic from the left, the keys for a given `status` value are spread all over the index — they sit inside every `userId` group. So the index is usable only when the query pins the leading field. The usable **index prefixes** are: - `{ userId: ... }` - `{ userId: ..., status: ... }` - `{ userId: ..., status: ..., createdAt: ... }` A query on `{ status: "OPEN" }` alone gets no useful seek from this index; the planner will normally choose a collection scan or a different index. A query on `{ createdAt: { $gt: ... } }` alone is in the same position. Note what the rule is *not* about: the order in which you write the fields in the query document is irrelevant. `{ status: "OPEN", userId: 42 }` and `{ userId: 42, status: "OPEN" }` are the same query and both use the index. What matters is which fields are present, not how they are typed. ## Skipping a middle field A filter on `userId` and `createdAt` but not `status` can still use the index, but only partially: MongoDB seeks on `userId`, and then `createdAt` cannot be used as a tight contiguous bound because within one `userId` the `createdAt` keys are grouped by `status` first. The index is still far better than a collection scan, but it is not as good as an index whose key order matches the query. ## Key order in design The order of the keys is the whole design decision. Put fields you match by equality before the field you sort or range-scan on, so that the sorted or ranged values sit in one contiguous run of the index. Given `find({ userId: 42, status: "OPEN" }).sort({ createdAt: -1 })`, this index is ideal: the two equality matches select one contiguous block, and within that block `createdAt` is already in descending order, so no separate sort step is needed. ## Direction and sorting Directions only ever matter relative to each other. This index can produce: - `sort({ userId: 1, status: 1, createdAt: -1 })` — walk the index forward. - `sort({ userId: -1, status: -1, createdAt: 1 })` — walk it backward; every key direction is flipped. It cannot produce `sort({ userId: 1, status: 1, createdAt: 1 })` from the index alone, because that ordering exists nowhere in the structure — no single traversal yields two fields forward and one backward. If a query pins the leading fields with equality, though, direction on those pinned fields stops mattering: with `userId` and `status` fixed to single values, a sort on `createdAt` alone can still be served, in either direction, by scanning that one contiguous block forward or backward. ## Consequences for the index set Because every prefix of a compound index is itself usable, an index on `{ userId: 1, status: 1 }` is redundant when `{ userId: 1, status: 1, createdAt: -1 }` exists — the wider index serves everything the narrower one does, at slightly larger key size. The reverse is not true: `{ status: 1, userId: 1 }` is a genuinely different index serving a different set of queries. Recognising the redundant-prefix case is how index sets get trimmed without losing coverage. ## Answering well List the three prefixes explicitly, say plainly that `status` alone gets nothing, then show the equality-before-sort design reasoning on a concrete query. Finish with the direction rule — forward or exactly inverted — because that is the part most candidates have never thought about.