Multi-key indexing (array field indexing)

The Concept of Multikey Indexes

Multikey indexes are a specialized type of index in MongoDB designed for array fields. When a field contains an array value, MongoDB automatically creates an index entry for each element in the array rather than a single index entry for the entire array. This indexing mechanism enables efficient queries that match single or multiple elements within arrays.

// Example document structure
{
  _id: 1,
  name: "Product A",
  tags: ["electronics", "digital", "smart home"],
  ratings: [4, 5, 3, 4]
}

How to Create Multikey Indexes

The syntax for creating multikey indexes is the same as for regular indexes. MongoDB automatically detects whether a field is an array and decides whether to create a multikey index:

// Create multikey indexes
db.products.createIndex({ tags: 1 })
db.products.createIndex({ ratings: -1 })

// View index information
db.products.getIndexes()

How Multikey Indexes Work

When query conditions involve array elements, MongoDB uses multikey indexes for efficient lookups:

1. For equality matches, MongoDB finds all arrays containing the specified value
2. For range queries, MongoDB checks if any array elements satisfy the condition
3. For the $elemMatch operator, MongoDB ensures a single array element meets all conditions

// Example queries using multikey indexes
db.products.find({ tags: "electronics" })
db.products.find({ ratings: { $gt: 4 } })
db.products.find({ 
  ratings: { 
    $elemMatch: { $gt: 3, $lt: 5 } 
  } 
})

Limitations of Multikey Indexes

1. Only one array field can be included in a compound index

   // Incorrect index creation (two array fields)
   db.products.createIndex({ tags: 1, ratings: 1 }) // Error
   
   // Correct compound index (one array field + non-array field)
   db.products.createIndex({ tags: 1, name: 1 }) // Success
   

2. Multikey indexes cannot fully support covered queries

3. Hash indexes cannot be multikey indexes

4. Multikey indexes may have high cardinality, affecting query performance

Performance Optimization for Multikey Indexes

1. Use $elemMatch to ensure queries operate on single array elements

   // More efficient query
   db.products.find({
     ratings: {
       $elemMatch: { $gt: 3, $lt: 5 }
     }
   })
   

2. Create compound indexes with non-array fields

   db.products.createIndex({ category: 1, tags: 1 })
   

3. Exercise caution when indexing large arrays, as it may impact write performance

Special Query Scenarios for Multikey Indexes

1. Querying array length

   db.products.createIndex({ "tags": 1, "ratings": 1 })
   db.products.find({ 
     tags: { $size: 3 } 
   }).explain("executionStats")
   

2. Querying specific array positions

   db.products.createIndex({ "ratings.0": 1 })
   db.products.find({ 
     "ratings.0": { $gt: 4 } 
   })
   

3. Querying nested arrays

   {
     _id: 2,
     name: "Product B",
     reviews: [
       { score: 5, comment: "Excellent" },
       { score: 3, comment: "Average" }
     ]
   }
   
   db.products.createIndex({ "reviews.score": 1 })
   db.products.find({ 
     "reviews.score": 5 
   })
   

Combining Multikey Indexes with Text Search

When arrays contain strings, you can create text indexes for full-text search:

db.products.createIndex({ tags: "text" })
db.products.find({
  $text: { $search: "electronics digital" }
})

Monitoring Multikey Index Usage

Use the explain() method to analyze whether queries effectively use multikey indexes:

db.products.find({ tags: "electronics" }).explain("executionStats")

Check the output for:

• stage: "IXSCAN" indicates index scan usage
• indexName: confirms which index was used
• keysExamined: number of index keys examined

Storage Considerations for Multikey Indexes

1. Each array element creates a separate index entry
2. Large arrays significantly increase index size
3. Frequently updated array fields may cause index fragmentation

// View index size
db.products.stats()
db.products.totalIndexSize()

Interaction Between Multikey Indexes and Aggregation Pipelines

In aggregation pipelines, multikey indexes can optimize $unwind and $match stages:

db.products.aggregate([
  { $match: { tags: "electronics" } },  // Uses multikey index
  { $unwind: "$tags" },
  { $group: { _id: "$tags", count: { $sum: 1 } } }
])

Dropping and Rebuilding Multikey Indexes

Remove unnecessary multikey indexes to free storage space:

db.products.dropIndex("tags_1")

Rebuilding indexes can resolve fragmentation issues:

db.products.reIndex()