MongoDB - Advanced Indexing

MongoDB - Advanced Indexing

Beyond single-field indexes, MongoDB offers a variety of powerful index types to optimize complex queries. This chapter covers indexes on embedded documents and arrays (multikey indexes), and introduces other specialized index types.

Let’s use a sample document in a products collection:

db.products.insertOne({
  "name": "Wireless Keyboard",
  "inventory": {
    "warehouse": "A",
    "quantity": 45
  },
  "tags": ["electronics", "computer", "accessories"],
  "ratings": [5, 4, 5, 5, 4]
})

Indexing Embedded Document Fields

To optimize queries on fields within an embedded document, you use ‘dot notation’ in your createIndex command. This allows you to target the nested field directly.

// Create an index on the 'quantity' field inside the 'inventory' sub-document
db.products.createIndex({ "inventory.quantity": -1 }) // -1 for descending order

This index will efficiently serve queries that filter or sort by the inventory quantity:

// This query can use the index to find low-stock items quickly
db.products.find({ "inventory.quantity": { $lt: 10 } })

Multikey Indexes (Indexing Array Fields)

When you create an index on a field that holds an array value, MongoDB creates a ‘multikey index’. It adds an index key for each element in the array. This allows for efficient querying to find documents where the array field contains a specific value.

// Create a multikey index on the 'tags' array
db.products.createIndex({ tags: 1 })

This index allows MongoDB to quickly find all products tagged with ‘computer’:

> db.products.find({ tags: "computer" }).explain('executionStats')
// The explain plan's winningPlan will show an IXSCAN on the tags_1 index.
// It will also indicate 'isMultiKey: true'.

Other Important Index Types

MongoDB supports several other specialized index types for different use cases:

• Compound Indexes: An index on multiple fields. The order of fields in a compound index is very important as it determines which queries the index can support. Our covered queries example { status: 1, username: 1 } was a compound index.
• Text Indexes: Used for searching string content in a collection. They support language-specific stop words and stemming. A collection can have at most one text index.
• Geospatial Indexes: Used to optimize queries on location data (e.g., longitude, latitude). They allow you to find points within a certain radius, a polygon, or find the nearest points to a location.
• TTL (Time-To-Live) Indexes: A special single-field index that allows you to automatically remove documents from a collection after a certain amount of time. Ideal for session data, logs, or any temporary information.

Best Practices for Indexing

Follow the ESR (Equality, Sort, Range) rule when creating compound indexes: fields for exact matches should come first, followed by fields for sorting, and finally fields for range queries.

Create indexes to support your application’s specific queries. Analyze slow queries using the Database Profiler or .explain() to identify missing indexes. Remember that while indexes dramatically speed up reads, they add a small overhead to write operations, so avoid creating indexes that are never used.