Master MongoDB Indexes: Query Plans, Creation, and Optimization Tips

1. View Query Plan

Use db.user.find({"username":"xxx"}).explain() or db.doc.find({"es_y":"2014"}).explain() to retrieve execution details. The output includes fields such as cursor (whether an index is used), indexOnly (covering index), nscanned and nscannedObjects (documents scanned), isMultiKey , millis (execution time), n (result count), scanAndOrder , and nYields .

2. Create Index

db.user.ensureIndex({"username":1})

3. List All Indexes in a Collection

db.doc.getIndexes()

4. View Current Operations

db.currentOp()

returns an object with an inprog array describing active operations, e.g.,

{"opid":486,"active":true,"secs_running":2,"op":"getmore",...}

.

5. Index Recommendations

A collection can have up to 64 indexes, but typically no more than two to avoid write performance degradation.

6. Create Compound Index

db.user.ensureIndex({"key1":1,"key2":1})

7. In‑Memory Sorting

If sorting exceeds the 32 MB limit, MongoDB throws an error.

8. Force a Specific Index

Use .hint({<em>index specification</em>}) in a query.

9. Covering Index

An index that contains all fields required by the query, eliminating the need to fetch the full document.

10. Implicit Index

Example: {"age":1,"username":1} can serve as an implicit {"age":1} index.

11. Array Field Limit

Only one array field is allowed per index to prevent index explosion.

12. Multikey Index

Occurs when an indexed field is an array; it cannot be converted to a non‑multikey index without rebuilding.

13. Compound Index Order

Place high‑cardinality keys first to reduce the number of scanned documents.

14. Force Full Collection Scan

.hint({"$natural":1})

15. Create Unique Index

db.user.ensureIndex({"username":1},{"unique":true})

. Keys larger than 8 KB are not subject to the uniqueness constraint.

16. Create Compound Unique Index

Use ensureIndex with {"unique":true} on multiple fields.

17. Duplicate Keys on Unique Index

db.user.ensureIndex({"username":1},{"unique":true,"dropDups":true})

18. Unique Index and Null

Null is treated as a distinct value that must be unique; to change this behavior, adjust the index definition.

19. Sparse Index

db.ensureIndex({"email":1},{"unique":true,"sparse":true})

indexes only documents that contain the field, allowing missing fields to be ignored while enforcing uniqueness on existing values.

20. Index Management

All index metadata resides in the system.indexes collection, which is read‑only. Use ensureIndex to create and dropIndex or dropIndexes to remove indexes.