MongoDB Indexing Best Practices: Real‑World Tips & Common Pitfalls

The previous part introduced MongoDB's "document" data type; if you missed it, refer to the earlier article. This series shares the DBA team's accumulated MongoDB usage tips from daily operations, along with real‑world issues and solutions, and we welcome any MongoDB questions. This article presents index usage considerations and online case studies.

Index

MongoDB’s compound index strategy follows MySQL’s “leftmost prefix” rule.

Index name length should not exceed 128 characters.

Evaluate query scenarios comprehensively and, when possible, merge single‑field indexes into compound indexes to reduce the total number of indexes, in line with points 1 and 2. 【Case 8】MongoDB compound index rules follow the leftmost principle, e.g., for index {a:1,b:1,c:1}, the following queries can use it: {a:1}, {a:1,b:2}, {a:1,b:2,c:3}, {}. Queries that cannot use the index: {b:1}, {b:1,c:2}, {c:2}. To support both {a:...} and {a:...,b:...} queries, create a compound index {a:1,b:1} instead of a separate {a:1} index.

When creating a compound index, assess the fields and place high‑cardinality fields first. 【Case 9】Query: {name: "baidu", status: 0} Index: {status: 1, name: 1} The index matches the query, but because status=0 matches 99% of the 1.5 million documents, the scan is still large. Reordering the index to {name: 1, status: 1} lets the high‑cardinality name field filter first, reducing query time to 3‑5 ms.

Index creation can be slow on large datasets, so evaluate and create indexes on demand before data grows massive.

MongoDB creates indexes with a database‑level lock, blocking reads/writes on the database during index build; therefore, involve DBA when adding indexes. 【Case 10】A large collection (3 million documents) was indexed, locking the entire database. In MongoDB 2.4, the "background": true option moves the build to the background on the primary, but the secondary still blocks. In MongoDB 2.6, the background build also runs on the secondary, though it can be very slow for huge collections. DBAs can use a rolling approach: switch the primary, take the secondary offline, add the index, then switch back, avoiding service disruption.

MongoDB supports TTL indexes that automatically delete documents older than a specified number of seconds, typically running during low‑traffic periods.

If your data includes geographic coordinates, you can create MongoDB’s 2d or 2dsphere geospatial indexes, but they are different and should not be mixed. 2d: suitable for point‑to‑point indexing on planar maps or time‑continuous data (e.g., game maps). 2dsphere: supports points, lines, and polygons on a spherical surface (Earth). Using a 2d index on a sphere causes distortion near the poles and inaccurate results.

MongoDB’s text search index is still experimental and not recommended for production use.

Since MongoDB 2.4, the Index Intersection (ICP) feature can reduce the number of indexes needed. For example, a compound index {x:1, y:1, z:1} can satisfy a query {x:1, z:1} when the x field has high cardinality and matches few documents, eliminating the need for a separate {x:1, z:1} index. However, ICP may be less efficient than a dedicated index.

We hope the above helps; the next article will cover operational performance topics.