sdlc.dev

MongoDB path · Finding things fast · Topic 5 of 12

Indexes: compound, multikey, TTL, partial

Not started

MongoDB's default index is a B-tree over one or more fields, kept in key order. A compound index on { status: 1, createdAt: 1 } is sorted by status, and by date within each status, much like a composite index in PostgreSQL. What makes MongoDB indexes different is how they deal with arrays and the special kinds built on top: TTL indexes that delete expired documents and partial indexes that cover only some of them.

Why it matters: In MongoDB almost every slow query is a missing or badly ordered index. Each extra index also slows every write and takes cache memory, so you want few indexes that each serve several queries.

  1. 1Equality, then sort, then range

    Put fields tested for equality first, then the sort field, then range filters. The index then hands back matches already in order.

  2. 2Arrays make an index multikey

    Indexing an array field creates one key per element. A compound index can include at most one array field per document.

  3. 3TTL indexes delete for you

    A single-field index on a date with expireAfterSeconds lets a background task remove old documents, within about a minute of expiry, not to the second.

  4. 4Partial indexes index only some documents

    A partialFilterExpression keeps the index small; queries must include that filter to use it.

1

How it works

The explanation, with examples you can run.

Field order: equality, sort, range

MongoDB's guideline for compound indexes is ESR: fields matched by equality first, then the fields you sort by, then fields with range conditions. Equality narrows the index to one contiguous slice. If the sort field comes next, that slice is already in the order you asked for, so the server reads it in order and stops after limit. Put the range field before the sort and the matching entries come back ordered by the range field, so they must all be collected and sorted in memory.

The same query against two indexes on 200,000 orders, forced with hint():

db.orders.find({ status: "paid", totalCents: { $gte: 10000 } }).sort({ createdAt: -1 }).limit(10)
{ status: 1, totalCents: 1, createdAt: 1 }  FETCH <- SORT <- IXSCAN   keys examined: 25111{ status: 1, createdAt: 1, totalCents: 1 }  LIMIT <- FETCH <- IXSCAN  keys examined: 24

The planner picked the second one by itself. The guideline has an exception the manual also notes: if the range condition is very selective, putting it before the sort (ERS) can read fewer keys than ESR, at the price of a small sort.

This toy shows why, with twelve orders and an index as a plain sorted array:

⌘/Ctrl + Enter
OutputNot run yet

Press Run to see what your code prints, in order.

Runs in this browser, in an isolated frame with no access to this page. console.log, info, warn and error are shown; code runs as a classic script, so use an async function for await.

Multikey indexes

Index a field that holds an array and MongoDB adds one key per element; explain reports isMultiKey: true. That is how find({ "items.sku": "sku-7" }) uses an index on items.sku. A compound index may include at most one array-valued field per document; inserting { a: [1, 2], b: [3, 4] } into a collection with an index on { a: 1, b: 1 } fails with cannot index parallel arrays [b] [a]. Large arrays mean many keys, which is one more reason to keep arrays bounded.

TTL and partial indexes

db.sessions.createIndex({ lastSeen: 1 }, { expireAfterSeconds: 3600 })db.orders.createIndex(  { customerId: 1, createdAt: -1 },  { name: "pending_by_customer", partialFilterExpression: { status: "pending" } })

A TTL index is a single-field index on a date. A background task on the primary runs every 60 seconds and deletes documents whose date is older than expireAfterSeconds, so expired documents can linger for a minute or more under load. Deletes replicate like any other write.

A partial index stores only documents matching its filter, so it is smaller and cheaper to maintain. In the run above, find({ customerId: 42, status: "pending" }) used pending_by_customer and find({ customerId: 42 }) did not. With a unique option, a partial index enforces uniqueness only among matching documents.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.A query filters on status (equality) and totalCents (range) and sorts by createdAt. Which index follows the ESR guideline?
  2. 2.Why does creating { a: 1, b: 1 } fail for a document like { a: [1, 2], b: [3, 4] }?
  3. 3.A partial index on { customerId: 1 } has partialFilterExpression { status: "pending" }. Which query can use it?

0 of 3 right so far. Get all three to finish this topic.

Go deeper