Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Indexes and Performance

Compound, Text and TTL Indexes

Learn compound index field order and the ESR rule, text indexes with $text search, TTL indexes that expire documents, partial indexes, and runnable stand-ins for each idea.

A single-field index helps a query that filters on that field. Most real queries combine several things: "this user's posts, newest first", "active products in a category under a price". For these, MongoDB needs a compound index. This lesson also covers text indexes for word search and TTL indexes that delete old documents by themselves.

Basics of declaring indexes are in Indexes in Mongoose.

Compound indexes and field order

A compound index sorts documents by the first field, then by the second within equal first values, and so on, like a phone book sorted by last name then first name.

JavaScript
postSchema.index({ author: 1, createdAt: -1 });

That index efficiently supports:

  • find({ author }): uses the prefix author.
  • find({ author }).sort({ createdAt: -1 }): finds the author, and the results are already in order, so no in-memory sort.
  • find({ author, createdAt: { $gte: d } }).

It does not efficiently support find({ createdAt: { $gte: d } }) alone, because the index is organized by author first. The rule is the prefix rule: an index on { a, b, c } can serve queries on a, on a,b, and on a,b,c, but not on b alone.

Node.js Why order matters: prefix rule (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The ESR rule

To order the fields of a compound index for queries that mix equality, sort and range conditions, use Equality, Sort, Range:

  1. Fields tested for equality first (status: 'active').
  2. Then the fields you sort by.
  3. Then range conditions ($gt, $lt, $in with many values, $ne).

For find({ status: 'active', price: { $lt: 50 } }).sort({ createdAt: -1 }), the best index is { status: 1, createdAt: -1, price: 1 }. Equality narrows to one block, the sort needs no extra step because the block is already ordered, and the range is checked on index keys rather than on documents. Verify with explain(): you want IXSCAN and no SORT stage.

A text index tokenizes strings into lowercase stemmed words, so you can search by word and get relevance scores.

JavaScript
articleSchema.index({ title: 'text', body: 'text' }, { weights: { title: 5, body: 1 }, name: 'article_text' });

const hits = await Article.find(
  { $text: { $search: 'mongoose index "compound key" -sql' } },
  { score: { $meta: 'textScore' } },
).sort({ score: { $meta: 'textScore' } });

Rules of the road: a collection can have only one text index (but it may cover several fields); $text matches whole stemmed words, not substrings (mong will not match mongoose); quotes mean a phrase, a leading - excludes a word. For autocomplete, typo tolerance or faceted search at scale, use a dedicated engine such as MongoDB Atlas Search or Meilisearch; the built-in text index is best for simple search.

Node.js What a text index stores (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

TTL indexes: documents that expire

A TTL (time to live) index on a date field tells MongoDB to delete documents once the date is older than a number of seconds. It suits sessions, password-reset tokens, one-time codes and temporary logs.

JavaScript
const sessionSchema = new Schema({
  userId: Schema.Types.ObjectId,
  createdAt: { type: Date, default: Date.now, expires: 60 * 60 * 24 }, // 24 hours
});
// equivalent: sessionSchema.index({ createdAt: 1 }, { expireAfterSeconds: 86400 });

Details: a background task runs about once a minute, so documents can outlive their expiry by up to a minute or more under load; do not rely on exact timing, and re-check expiry in your code (if (token.expiresAt < new Date())). TTL works only on a single date field (not compound) and only with Date values. To expire at an exact per-document time, set expireAfterSeconds: 0 and store the exact expiry in the field.

Node.js Expire at a per-document time (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Partial and covered queries

A partial index indexes only documents matching a filter, making it smaller and cheaper:

JavaScript
orderSchema.index({ customer: 1, createdAt: -1 }, { partialFilterExpression: { status: 'open' } });

A query can use it only if its own filter implies the partial filter (it must include status: 'open'). A covered query is answered entirely from the index: every filtered and returned field is in the index and _id is excluded (.select('author createdAt -_id') combined with .lean()). MongoDB then never reads a document at all.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A compound index is { author: 1, createdAt: -1 }. Which query can NOT use it efficiently?

    Without the author prefix, matching createdAt values are scattered across the index (the prefix rule).
  2. 2. What does the ESR rule say about field order?

    Equality fields first, then sort fields, then range fields.
  3. 3. How many text indexes can one collection have?

    A collection can have a single text index, which may include several string fields.
  4. 4. Why should you still check expiry in code when you have a TTL index?

    MongoDB's TTL monitor runs about once a minute, so expiry is approximate.

Summary

  • Compound indexes follow the prefix rule; field order matters.
  • Order fields Equality, Sort, Range, then confirm with explain().
  • A text index tokenizes words for $text search; one per collection, no substring matching.
  • TTL indexes (expires/expireAfterSeconds) delete old documents roughly once a minute.
  • Partial indexes and covered queries make indexes smaller and queries cheaper.

Next: Lean, select and pagination