M201: Chapter 3: MongoDB Index Operations

Chapter 3: Index Operations

Building Indexes

Hybrid Index Build(new in 4.2)

db.movies.createIndex({title: 1})
db.movies.createIndex({title: 1}, {background: true})

  • One index build type
  • We can now build indexes quickly and without the need to lock the database.

MongoDB now only has one index buid type available.

Query Plans

What are query plans?

MongoDB plans how to perform a query using some indexes.

db.restaurants.find({"address.zipcode": {$gt: '50000'}, cuisine: 'Sushi'}).sort({stars: -1})

// index

{"address.zipcode": 1, cuisine: 1}

Query plans are cached so that plans do not need to be generated and compared against each other every time a query is executed.

Forcing Indexes with Hint

Overriding MongoDB’s default index selection with hint()

	{name: "John Doe", zipcode: {$gt:"63000"}}
).hint({name: 1, zipcode: 1})

What is the method that forces MongoDB to use a particular index?

Resource Allocation for Indexes

Basic Benchmarking Part 1

  • Public Test Suite
  • Specific or Private Testing Environment

- sysbench - iibench-mongodb

Database Server Benchmarking

  • YCSB
  • TPC

Distributed Systems Benchmarking
- Linearization - Serialzation - Fault tolerance

  • HiBench
  • Jepsen

Basic Benchmarking Part 2

  • POCDriver

What type of strategy and tools should we be using to performance benchmark a MongoDB installation?

Publicly available tools, including correct database variations

Lab 3.1: Explain Output

In this lab you’re going to determine which index was used to satisfy a query given its explain output.

The following query was ran:

> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED)

Which resulted in the following output:

  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "m201.restaurants",
    "indexFilterSet": false,
    "parsedQuery": "REDACTED",
    "winningPlan": {
      "stage": "SORT",
      "sortPattern": {
        "name": 1
      "inputStage": {
        "stage": "SORT_KEY_GENERATOR",
        "inputStage": {
          "stage": "FETCH",
          "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": "REDACTED",
            "indexName": "REDACTED",
            "isMultiKey": false,
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 1,
            "direction": "forward",
            "indexBounds": "REDACTED"
    "rejectedPlans": [ ]
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 3335,
    "executionTimeMillis": 20,
    "totalKeysExamined": 3335,
    "totalDocsExamined": 3335,
    "executionStages": "REDACTED"
  "serverInfo": "REDACTED",
  "ok": 1

Given the redacted explain output above, select the index that was passed to hint.

{ "address.state": 1, "stars": 1, "name": 1 }