5.3 Multikey Indexes & Array Indexing Constraints

Multikey Indexes & Array Indexing Constraints

Exam Focus: The MongoDB Certified Associate Developer Exam tests the automatic creation mechanics of Multikey Indexes, indexing arrays of embedded subdocuments using dot notation and $elemMatch, the strict restriction prohibiting compound multikey indexes from spanning multiple array fields ("parallel arrays"), and the write amplification/storage tradeoffs of multikey indexing.

---

Multikey Index Mechanics & Entry Generation

In MongoDB, documents frequently contain arrays of scalar values (strings, integers, dates) or arrays of embedded subdocuments. When you create an index on a field that contains an array value in any document, MongoDB automatically provisions a Multikey Index.

How Multikey Indexing Works

Instead of generating a single index key pointing to the document, MongoDB creates an individual index entry for every distinct element in the array. Each of these index entries points back to the same underlying document via its RecordId.

Document in Collection:
{
  _id: ObjectId("66d5a1b2c3d4e5f60789001"),
  title: "MongoDB Performance Tuning",
  tags: [ "nosql", "indexing", "wiredtiger" ]
}

Index on { tags: 1 }:
  +----------------+----------------+
  |   Index Key    |    RecordId    |
  +----------------+----------------+
  | "indexing"     | -> RecordId_01 |
  | "nosql"        | -> RecordId_01 |
  | "wiredtiger"   | -> RecordId_01 |
  +----------------+----------------+

Automatic Runtime Conversion

You do not need to use special syntax to create a multikey index. You execute standard createIndex() syntax:

db.articles.createIndex({ tags: 1 });

If the collection is empty, MongoDB initializes the index metadata. The moment a document containing an array in the tags field is inserted, MongoDB automatically sets the index property isMultikey: true in the index catalog. Once marked as multikey, the index retains this setting permanently.

---

Indexing Arrays of Embedded Subdocuments

Applications frequently store arrays of nested objects, such as items in a shopping cart, comments on a blog post, or telemetry logs from IoT sensors. Multikey indexes support dot notation to index specific fields within embedded subdocument arrays.

Example: Indexing Nested Subdocument Arrays

// Document structure in 'courses' collection:
{
  _id: 101,
  title: "Database Systems",
  curriculum: [
    { module: 1, topic: "Relational Theory", duration_hours: 4 },
    { module: 2, topic: "BSON & Document Model", duration_hours: 6 },
    { module: 3, topic: "Indexing Strategies", duration_hours: 8 }
  ]
}

// Create a multikey index on the nested 'topic' field inside 'curriculum'
db.courses.createIndex({ "curriculum.topic": 1 });

Querying Embedded Arrays with $elemMatch

When querying documents where multiple conditions must be satisfied by the same single array element, developers use the $elemMatch operator. Multikey indexes optimize $elemMatch queries by scanning the compound subdocument keys efficiently:

// Query matching courses having a module that is BOTH module >= 2 AND duration_hours >= 6
db.courses.createIndex({ "curriculum.module": 1, "curriculum.duration_hours": 1 });

db.courses.find({
  curriculum: {
    $elemMatch: { module: { $gte: 2 }, duration_hours: { $gte: 6 } }
  }
});

---

The Parallel Array Restriction (The Fundamental Constraint)

The most heavily tested multikey index topic on the certification exam is the Parallel Array Restriction.

The Rule

[!CAUTION] The Parallel Array Rule: A compound multikey index CANNOT index more than one array field per document. You cannot create a compound index where two or more indexed fields are arrays within the same document.

Why Parallel Arrays are Prohibited: The Cartesian Product Explosion

If MongoDB allowed indexing two independent array fields in a single compound index, the storage engine would be forced to compute and persist the full Cartesian Product of both arrays for every single document.

Suppose a document has two array fields:

• tags: Array of 100 string elements
• categories: Array of 100 string elements

If MongoDB built a compound index { tags: 1, categories: 1 }, a single document would require: $\text{Total Index Entries} = 100 \times 100 = 10,000 \text{ B-tree entries!}$

If a document had two arrays with 1,000 items each, a single insert would generate 1,000,000 index keys. This explosive write amplification would crash database memory, saturate disk I/O, and exhaust storage capacity.

Runtime Error Behavior: cannot index parallel arrays

MongoDB enforces this constraint at both index creation time and document insertion time:

1. At Index Creation Time: If existing documents in the collection already contain arrays in more than one of the target fields, createIndex() fails immediately:

// Existing document: { _id: 1, tags: ["tech", "code"], authors: ["Alice", "Bob"] }

db.posts.createIndex({ tags: 1, authors: 1 });
// Throws MongoServerError: cannot index parallel arrays [authors] [tags]

1. At Document Write Time: If the compound index { tags: 1, authors: 1 } was created when documents only had scalar values, and an application subsequently attempts to insert a document where both fields are arrays, the write operation is aborted:

db.posts.insertOne({
  title: "Architecture Post",
  tags: [ "nosql", "cloud" ],    // Array field 1
  authors: [ "Carol", "Dave" ]   // Array field 2 -> FAILS!
});
// Throws MongoServerError: cannot index parallel arrays [authors] [tags]

Valid Compound Multikey Indexes (One Array + Scalars)

A compound index can index one array field alongside one or more scalar (non-array) fields:

// VALID: 'department' (scalar), 'tags' (array), 'created_at' (scalar)
db.articles.createIndex({
  department: 1,    // Scalar String
  tags: 1,          // Array of Strings (Multikey)
  created_at: -1    // Scalar Date
});

---

Performance Tradeoffs & Operational Overhead

While multikey indexes are essential for array search capabilities, they introduce distinct performance tradeoffs:

1. Write Amplification: When inserting or updating a document with an array of $N$ elements, MongoDB must write or re-index $N$ discrete B-tree leaf nodes. Large arrays substantially reduce write throughput.
2. WiredTiger Cache Footprint: Because each array element creates a separate index key, multikey indexes consume significantly more memory than scalar indexes. A multikey index on a high-cardinality array can quickly displace other working sets from RAM.
3. Covered Query Incompatibility: In most query scenarios involving array elements, multikey indexes cannot cover queries. Even if all query and projection fields are in the index, MongoDB cannot reconstruct the original array structure, bounds, and ordering without performing a FETCH stage on the actual document.

Scalar Document Write:    1 Document Insert ===> 1 Index Key Inserted

Multikey Document Write:  1 Document Insert (10 Tags) ===> 10 Distinct Index Keys Inserted
                          (10x B-Tree Updates in Memory & Journal)