12:24
#54 Creating a Compound Index | Understanding Index in MongoDB | MongoDB Complete Course 2025
procademy
Overview
This video explains the concept and application of compound indexes in MongoDB. It differentiates compound indexes from single-field indexes and highlights why certain fields, like booleans or low-cardinality fields (e.g., gender), are not ideal for indexing. The core of the lecture focuses on how to create a compound index on multiple fields, emphasizing that the order of fields in the index definition is crucial for query performance. It demonstrates how MongoDB utilizes compound indexes for queries that include the leading fields of the index, and how it falls back to collection scans if the leading fields are omitted.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- Indexes on fields with very few distinct values (low cardinality), like booleans (true/false) or gender (male/female), are generally inefficient.
- When a query on an indexed field returns a majority of the documents, the index becomes less effective or even detrimental to performance.
- MongoDB might still use an index on a low-cardinality field, but it involves extra steps (index scan then document fetch), negating performance benefits.
Understanding these limitations helps you avoid creating ineffective indexes that can slow down your database instead of speeding it up.
Indexing a 'gender' field with only 'male' and 'female' values is unlikely to improve query performance because a query for 'male' would likely return most of the documents.
- A compound index is a single index that covers two or more fields within a collection.
- Compound indexes allow for more efficient querying when multiple fields are used in the query criteria.
- You can define compound indexes on up to 31 fields.
Compound indexes can significantly optimize queries that filter or sort by multiple fields simultaneously, reducing the need for multiple single-field indexes.
Creating an index on both 'age' and 'gender' fields allows MongoDB to efficiently query documents based on combinations of age and gender.
- Compound indexes are created using the `createIndex()` method, specifying fields and their order (e.g., `{ age: 1, gender: 1 }`).
- The order of fields in the `createIndex()` definition is critical; it determines how the index is structured and utilized.
- An index defined as `{ age: 1, gender: 1 }` sorts data first by 'age' and then by 'gender' within each age group.
The order in which you define fields in a compound index directly impacts which queries can efficiently use that index.
An index created with `{ age: 1, gender: 1 }` will be structured with data sorted primarily by age, and secondarily by gender for documents with the same age.
- MongoDB can use a compound index if the query filters include the *leading* fields of the index, in order.
- Queries that include the first field of the index (e.g., 'age' in `{ age: 1, gender: 1 }`) can utilize the index, even if subsequent fields are omitted.
- Queries that omit the first field of the index but include later fields (e.g., only 'gender' in `{ age: 1, gender: 1 }`) will *not* use the index and will result in a collection scan.
- The order of fields within the query's filter object does not matter; only the presence and order relative to the index definition matter.
This understanding is vital for designing effective queries that leverage your compound indexes, ensuring optimal performance.
A query `{ age: { $gte: 40 }, gender: 'male' }` will use an index on `{ age: 1, gender: 1 }`. A query `{ age: { $gte: 40 } }` will also use this index. However, a query `{ gender: 'male' }` will not use this index.
Key takeaways
- Avoid creating indexes on fields with very low cardinality (few unique values) as they offer little to no performance benefit.
- Compound indexes are single indexes that span multiple fields, improving performance for queries involving those fields.
- The order of fields specified when creating a compound index is crucial for its effectiveness.
- MongoDB can use a compound index if the query filters match the index's fields from left to right (the leading fields).
- If a query omits the leading field(s) of a compound index, MongoDB will not use that index and will perform a collection scan.
- The order of fields in the query's filter condition does not affect index usage, but the fields included and their relationship to the index's defined order do.
Key terms
Compound IndexSingle-Field IndexLow CardinalityIndex ScanCollection ScanLeading FieldsQuery Optimizer`createIndex()``explain()`
Test your understanding
- Why are indexes on boolean or gender fields generally not recommended in MongoDB?
- What is a compound index and how does it differ from a single-field index?
- How does the order of fields in the `createIndex()` command affect the performance of a compound index?
- Under what conditions will MongoDB utilize a compound index for a query?
- What happens if a query attempts to use a compound index but omits the first field defined in that index?