Compound and Text Indexes

10 min read

Compound Indexes and Text Search

A compound index covers multiple fields in one B-tree. It supports queries that filter or sort on any prefix of the index fields, making it far more versatile than two separate single-field indexes.

The ESR Rule

When designing compound indexes, follow the ESR rule: place Equality fields first, Sort fields second, and Range fields last. This order maximises index efficiency.

// Query: enrolled = true, sort by age, age > 18
// ESR order: enrolled (equality), age (sort+range)
db.students.createIndex({ enrolled: 1, age: 1 });

// This index supports both:
// db.students.find({ enrolled: true }).sort({ age: 1 })
// db.students.find({ enrolled: true, age: { $gt: 18 } }).sort({ age: 1 })

// Verify with explain
db.students.find({ enrolled: true, age: { $gt: 18 } })
  .sort({ age: 1 })
  .explain("executionStats");

Text Indexes

A text index tokenises string fields and stores stem words, enabling full-text search with relevance scoring. Only one text index is allowed per collection, but it can span multiple fields.

// Create a text index on title and description
db.articles.createIndex(
  { title: "text", description: "text" },
  { weights: { title: 10, description: 2 }, name: "article_text" }
);

// Search and sort by relevance score
db.articles.find(
  { $text: { $search: "mongodb aggregation" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } });

For production full-text search use Atlas Search (built on Lucene) — it supports fuzzy matching, autocomplete, and facets that the native $text operator does not.

Ready to test yourself?

Practice MongoDB— quiz & coding exercises