0Pricing
MongoDB Academy · レッスン

複合インデックスのプレフィックスルールとESR原則

Equality-Sort-Rangeのインデックス設計原則を複合インデックスに適用し、クエリのカバレッジを最大化します。

「複合インデックスのプレフィックスルールとESR原則」はCoddyKit上の無料MongoDB Academyレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはMongoDB Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 MongoDB Academyコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Why Compound Index Field Order Matters

A compound index on multiple fields can serve a wide range of queries — but only if the fields appear in the right order. MongoDB can use a compound index to satisfy a query only if the query's filter matches a prefix of the index. The ordering of fields in the index definition directly controls which queries benefit from it.

The Prefix Rule Explained

A compound index { a: 1, b: 1, c: 1 } can be used by queries that filter on: { a }, { a, b }, or { a, b, c }. These are the prefixes. A query filtering only on { b } or { b, c } cannot use this index — it would do a collection scan. The index is like a phone book sorted by last name, then first name: you can look up by last name alone or by last + first, but not by first name alone.

// Index: { status: 1, customerId: 1, createdAt: 1 }
db.orders.createIndex({ status: 1, customerId: 1, createdAt: 1 })

// Uses index (prefix: status)
db.orders.find({ status: 'pending' })

// Uses index (prefix: status + customerId)
db.orders.find({ status: 'pending', customerId: 'c001' })

// Does NOT use index (no leading prefix)
db.orders.find({ customerId: 'c001' })

The ESR Principle: Equality, Sort, Range

The ESR principle is a field-ordering rule for compound indexes: put Equality fields first, Sort fields second, and Range fields last. This ordering maximises the portion of the query the index can satisfy and minimises the number of index entries that must be examined. ESR is the most important compound index design rule in MongoDB.

// Query: find pending orders for customer c001, sorted by date,
// for dates after Jan 2025
// E: status = 'pending' (equality)
// S: createdAt (sort)
// R: customerId in ['c001','c002'] (range / $in)

// ESR-ordered index
db.orders.createIndex({ status: 1, createdAt: 1, customerId: 1 })

Why Equality Fields Come First

Equality predicates (field: value or $eq) narrow the index scan to a single, fixed value. Placing them first dramatically reduces the number of index entries the query planner needs to consider. Once equality has pinpointed the exact bucket of matching keys, the sort and range operations work on a much smaller dataset.

// E first: status equality narrows to ~5% of index
// Then sort on createdAt within that slice
// Then range on amount within that sorted slice
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })

db.orders.find({ status: 'shipped' })
  .sort({ createdAt: -1 })
  .hint({ status: 1, createdAt: 1, amount: 1 })

Why Sort Fields Come Before Range

Placing sort fields before range fields allows MongoDB to use the index to satisfy the sort without a blocking in-memory sort. If range fields come before sort fields, MongoDB must scan all matching range documents, sort them in memory, then return results — adding CPU and memory overhead. With sort fields second, results emerge from the index already in the correct order.

// Without ESR: range before sort forces in-memory sort
db.orders.createIndex({ status: 1, amount: 1, createdAt: 1 })

db.orders.find({ status: 'pending', amount: { $gt: 50 } })
  .sort({ createdAt: 1 })
// explain() shows: SORT stage (in-memory sort needed)

// With ESR: sort before range avoids in-memory sort
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })
// explain() shows: no SORT stage

Range Fields Last: Why It Works

Range predicates like $gt, $lt, $gte, $lte, $in, and regex span a contiguous portion of the index. By placing them last, MongoDB first narrows results with equality and delivers them in sort order, then applies the range check as a final filter. The index scan stays efficient because range does not break the sorted traversal order set by the sort fields.

// ESR applied correctly
// E: userId (equality)
// S: timestamp (sort)
// R: score (range)
db.events.createIndex({ userId: 1, timestamp: 1, score: 1 })

db.events.find({
  userId: 'u123',
  score: { $gte: 80 }
}).sort({ timestamp: -1 })

Handling $in: Range or Equality?

$in with a small list of values behaves more like equality and can be placed first. When the list is large, it acts more like a range and should go later in the index. A useful rule: if the $in list has fewer than 10–20 values and you query it frequently, treat it as equality (first). For large, dynamic lists, treat it as range (last).

// Small $in (2 values) — treat as equality, put first
db.orders.createIndex({ status: 1, createdAt: 1 })
db.orders.find({ status: { $in: ['pending', 'processing'] } })
  .sort({ createdAt: -1 })

// Large $in — treat as range, put last
db.orders.createIndex({ region: 1, createdAt: 1, userId: 1 })
db.orders.find({
  region: 'EU',
  userId: { $in: hundredsOfUserIds }
}).sort({ createdAt: -1 })

Verifying ESR With explain()

Always verify your index design with explain('executionStats'). Look for: IXSCAN (index scan) — good. COLLSCAN (collection scan) — missing index. SORT stage present — in-memory sort, index field order might be wrong. keysExamined / nReturned should be close to 1 for an optimal compound index.

db.orders.find({ status: 'pending', amount: { $gt: 50 } })
  .sort({ createdAt: 1 })
  .explain('executionStats')

// Good: { stage: 'IXSCAN', nReturned: 42, keysExamined: 44 }
// Bad:  { stage: 'COLLSCAN', nReturned: 42, docsExamined: 500000 }

The Prefix Rule and Partial Index Reuse

Thanks to the prefix rule, a single well-designed compound index can replace several single-field indexes. An index on { a: 1, b: 1, c: 1 } makes separate indexes on { a: 1 } and { a: 1, b: 1 } redundant. Fewer indexes means less write overhead and less memory pressure — important for write-heavy workloads where index maintenance adds latency to every insert, update, and delete.

// One compound index replaces three single-field indexes
db.users.createIndex({ country: 1, city: 1, age: 1 })

// Redundant (covered by compound prefix rule):
// db.users.createIndex({ country: 1 })          -- REDUNDANT
// db.users.createIndex({ country: 1, city: 1 }) -- REDUNDANT

Index Selectivity and Field Order

Beyond ESR, consider selectivity — how many documents share the same value. Put the most selective equality field first (fewest duplicates). For example, userId is more selective than status. Placing the more selective field first narrows the scan faster. When multiple equality fields exist, order them most-selective to least-selective for maximum performance.

// userId is highly selective (millions of users)
// status is low-selectivity (only 5 values)

// More efficient: selective equality field first
db.orders.createIndex({ userId: 1, status: 1, createdAt: 1 })

// Less efficient: low-selectivity field first
db.orders.createIndex({ status: 1, userId: 1, createdAt: 1 })

Putting ESR Into Practice

When designing a compound index, start by listing your top query's filter conditions and sort, then classify each field as E (equality), S (sort), or R (range). Build the index in that order. Run explain('executionStats') to confirm you see an IXSCAN with no SORT stage and a keysExamined/nReturned ratio near 1. Revisit the index whenever query patterns change.

// Practical checklist:
// Query: find users in 'NY' (E), sorted by signup (S), age > 18 (R)
// E: state = 'NY'
// S: signupDate
// R: age > 18
db.users.createIndex({ state: 1, signupDate: 1, age: 1 })

// Verify no in-memory sort and good key ratio:
db.users.find({ state: 'NY', age: { $gt: 18 } })
  .sort({ signupDate: -1 })
  .explain('executionStats')

Quick Check

Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.

Lesson Recap

In this lesson you learned: a compound index can only be used when the query matches a prefix of the index fields, the ESR principle dictates ordering fields as Equality, Sort, Range for maximum query coverage, and placing sort fields before range fields eliminates costly in-memory sort stages. Next up we compare index intersection versus compound indexes.

よくある質問

「複合インデックスのプレフィックスルールとESR原則」レッスンは無料ですか？

はい。「複合インデックスのプレフィックスルールとESR原則」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、MongoDB Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 MongoDB Academyコースには全4レッスンが含まれています。

「複合インデックスのプレフィックスルールとESR原則」で何を学びますか？

Equality-Sort-Rangeのインデックス設計原則を複合インデックスに適用し、クエリのカバレッジを最大化します。 ブラウザで直接実行するハンズオンコードでMongoDB Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

MongoDB Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのMongoDB Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「複合インデックスのプレフィックスルールとESR原則」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このMongoDB Academyレッスンでコードを書いて実行できますか？

はい。すべてのMongoDB Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. データベースプロファイラーと低速クエリログ
  2. 複合インデックスのプレフィックスルールとESR原則
  3. インデックスインターセクションと複合インデックスの比較
  4. 集約パイプラインの最適化のヒント
← MongoDB Academyに戻る