MongoDB Index Types: How to Create Single-Field and Compound Indexes?

What is a MongoDB Index?¶

In MongoDB, an index is like the “table of contents” of a book—without it, you’d have to scan the entire book to find content; with it, you can quickly locate the target. The core role of an index is to accelerate queries, avoiding full collection scans (i.e., traversing the entire collection) and making queries faster and more efficient.

Why Build Indexes?¶

Imagine you have a collection with 1 million user records and need to find users aged 25. Without an index, MongoDB would check each document one by one, potentially scanning hundreds of thousands of entries. However, if you create an index on the age field, MongoDB can directly locate all documents with age=25 via the index, instantly improving efficiency.

Single-Field Index: The Simplest Index Type¶

A single-field index is created for a single field and is the most basic and commonly used index type.

Applicable Scenarios¶

• When queries only involve one field (e.g., name, age).
• When sorting by a single field (e.g., fetching the latest registered users by reg_time in descending order).

Creation Syntax¶

db.collectionName.createIndex({ fieldName: 1 })  // 1 for ascending, -1 for descending

Example¶

Suppose you have a students collection and frequently query students aged 20. You can create an index on the age field:

// Create an ascending index on the "age" field
db.students.createIndex({ age: 1 })  

// The index will be automatically used during querying
db.students.find({ age: 20 }).explain("executionStats")  

Notes¶

• The choice between ascending (1) and descending (-1) depends on query requirements. For example, to query students in reverse age order, use { age: -1 }.
• Single-field indexes only optimize queries that include the indexed field. For example, an index on age will not help with find({ name: "Xiaoming" })—only find({ age: x }) will use it.

Compound Index: Optimizing Queries Across Multiple Fields¶

A compound index is created for multiple fields and is suitable for scenarios requiring filtering or sorting on multiple fields simultaneously (e.g., querying users by “region + registration time”).

Core Principle: Left-Prefix Rule¶

The order of fields in a compound index is critical! MongoDB uses the index in the order defined (left to right). Only queries that include the leftmost field of the index can utilize the entire compound index.

Example 1: Correctly Using a Compound Index¶

Suppose you create a compound index { region: 1, reg_time: -1 } (sorted by region ascending, then registration time descending):
- ✅ Index-friendly query: find({ region: "Beijing", reg_time: { $gt: ISODate("2023-01-01") } }) (filters by region first, then registration time).
- ❌ Index-unfriendly query: find({ reg_time: { $gt: ISODate("2023-01-01") } }) (lacks the leftmost field region, violating the left-prefix rule).

Example 2: What If the Order Is Reversed?¶

If you create { reg_time: -1, region: 1 } (sorted by registration time descending, then region ascending):
- ✅ Index-friendly queries: find({ reg_time: { $gt: ISODate("2023-01-01") } }) (uses only reg_time) or find({ reg_time: { $gt: ISODate("2023-01-01"), region: "Beijing" } }).
- ❌ Index-unfriendly query: find({ region: "Beijing" }) (lacks the leftmost field reg_time).

Creation Syntax¶

db.collectionName.createIndex({ field1: 1, field2: -1 })  // Comma-separated, 1=asc, -1=desc

Example: E-commerce Order Queries¶

Suppose you have an orders collection and frequently query “region=Shanghai + order status=paid” with orders sorted by “order_time” descending. Create a compound index:

db.orders.createIndex({ region: 1, status: 1, order_time: -1 })  

This index optimizes:
- Filtering: region: "Shanghai" (leftmost field), status: "paid" (second field).
- Sorting: order_time: -1 (third field, descending).

Index “Pitfalls”: Don’t Let Indexes Become a Burden¶

More indexes are not always better. Over-indexing degrades write performance (inserts, updates, deletes require maintaining indexes). Key considerations:

1. Avoid duplicate indexes: Creating multiple indexes on the same field (e.g., { age: 1 } and { age: -1 }) wastes space and prevents both from being used simultaneously.
2. Index low-selectivity fields sparingly: If a field has high repetition (e.g., “gender” with only “male” and “female”), the index may offer little benefit and could even be slower than a full scan.
3. Only index frequently queried fields: Index fields queried 100 times daily, but avoid indexing fields rarely accessed.

Summary¶

• Single-field index: For single-field filtering/sorting, simple syntax, and basic usage.
• Compound index: For multi-field filtering + sorting, with the left-prefix rule (only queries including the leftmost field use the index).
• Key principle: Indexes should be “built as needed and moderately”—used for high-frequency queries to avoid degrading write efficiency.

For large datasets and complex queries, compound indexes significantly improve performance. Remember: Indexes accelerate queries, but are not a “silver bullet”—rational planning ensures MongoDB runs faster!