MongoDB Guide | Indexes

by Jacob Pelletier
Contact me for suggestions, comments, or just to say hello at jacob@yankeedo.io! 👋

Follow me on my journey in learning MongoDB.

I highly recommend checking out Mongo University!

What will we be covering in this guide

What is an index.
What is an index used for.
What to consider when using indexes.
How to use indexes.

What are indexes?

Indexes are special data structures that store a portion of the data in and ordered and easy to search way.

Indexes point to document identities, which let you look up things faster.

Indexes improve query performance, reduce disk I/O, and reduce the overall resources required.

All collections have at least one index, the _id field.

Without indexes, MongoDB reads all documents and sorts the results in memory.

With indexes, MongoDB only fetches the documents identified by the index based on the query.

Every query should use at least one index.

When we insert new data, we need up update the index structure. Therefore, while indexes improve read performance, indexes also have a write performance cost.

The most common index types are:

Single field indexes, indexes on one field only.
Compound, indexes from more than one field.

You can also use multikey indexes, which are indexes that operate on an array field.

Create A Single Field Index

Mongo Documentation

Single field indexes are indexes on a single field.🤯
They support queries and sorts on a single field.

We can create a single field index with:
db.coll.createIndex({fieldname:1}) where the 'createIndex' command creates the single field index, the field on which we create the new index will be passed to 'fieldname', and then the order of the sort is given (1 for ascending, -1 for descending).

In the sample airbnb database, here is how we would create a single field index on the (embedded) field of country.

In this same database, we can also create a single field index on the posting URL. This posting URL ought to be unique for every posting. Therefore we can enforce this uniqueness trait when we create a new single field index on host ID.

MongoDB Enforcing Index Uniqueness Docs

If we were to try to insert a new document with an existing posting URL, we would get a duplicate key error.

Check out the indexes on our sample database with db.listingsAndReviews.getIndexes().

Notice the 'listing_url' and 'address.country' indexes in the playground results.

Our indexes from the Atlas Cloud dashboard.

To check if any of these indexes are being used in a query, we can use the explain() command.

In this query, we find the listing in the United States.

The explain() command returns the query plan (seen above in the mongo playground results).

The winningPlan subsection lists the stages that are executed when this query is run. 'IXSCAN' uses the index ("indexName": "address.country_1") to find the country. The 'Fetch' stage reads only the documents that the index has identified.

Note that sorting listings by market (i.e. city) after identifying documents by country adds another stage (SORT) to it's winning scan.

If a common query does not use an index, we should think about adding an index to make this query more efficient (otherwise the query will continue to scan the entire collection).

Because indexes can be a tricky topic, I will include more examples from the MongoDB docs.

Create a Single Field Index;

db.customers.createIndex({
  birthdate: 1
})

Create a Unique Single Field Index

db.customers.createIndex({
  email: 1
},
{
  unique:true
})

View the Indexes used in a Collection

db.customers.getIndexes()

Check if an index is being used on a query a. The IXSCAN stage indicates the query is using an index and what index is being selected. b. The COLLSCAN stage indicates a collection scan is perform, not using any indexes. c. The FETCH stage indicates documents are being read from the collection. d. The SORT stage indicates documents are being sorted in memory.

db.customers.explain().find({
  birthdate: {
    $gt:ISODate("1995-08-01")
    }
  })
db.customers.explain().find({
  birthdate: {
    $gt:ISODate("1995-08-01")
    }
  }).sort({
    email:1
    })

Creating Multikey Indexes.

A multikey index is an index on an array field. While you can have multiple fields as part of an index, you can only include one array field per index.

Using multikey to find listings with the internet amenity.

Note that in the query above, MongoDB uses the multikey amenities index in the 'IXSCAN' stage. Then the documents can be fetched. The index values have each value (i.e. amenity) stored separately.

Because indexes can be a tricky topic, I will include more examples from the MongoDB docs.

Create a Single field Multikey Index

db.customers.createIndex({
  accounts: 1
})

View the Indexes used in a Collection

db.customers.getIndexes()

Compound Indexes

Compound indexes are indexes on multiple fields. This may include one multikey index if an array field is included.

The order of the fields in a compound index matters. Preferably, fields that can be tested by equality first, followed by fields that can be used in sort, followed by fields that can be used for range. The sort order of the field values also matters.

Equality tests are tests which examine fields based on a single value, such as findOne({"host_is_superhost": true}) or find({"bedrooms": 1}). This will reduce the processing time for a query.

Fields suitable for sort are those which can be sorted, for example sort({"number_of_reviews":-1}). This determines the order of the results and eliminates the need for in-memory sorts. Sort order is important if query results are sorted by more than one field and they mix sort order, for example sort({"number_of_reviews":-1, "price.numberDecimal":1}).

Check this query with the explain() command, and you should see our chosen compound key fields under the 'IXSCAN' stage in 'indexName' ("indexName": "bedrooms_1_number_of_reviews_-1_price.numberDecimal_1") in the example above.

Because indexes can be a tricky topic, I will include more examples from the MongoDB docs.

Create a Compound Index

db.customers.createIndex({
  active:1, 
  birthdate:-1,
  name:1
})

Order of Fields in a Compound Index Query:

db.customers.find({
  birthdate: {
    $gte:ISODate("1977-01-01")
    },
    active:true
    }).sort({
      birthdate:-1, 
      name:1
      })

Index:

db.customers.createIndex({
  active:1, 
  birthdate:-1,
  name:1
})

View the Indexes used in a Collection

db.customers.getIndexes()

Cover a query by the Index

db.customers.explain().find({
  birthdate: {
    $gte:ISODate("1977-01-01")
    },
  active:true
  },
  {name:1,
    birthdate:1, 
    _id:0
  }).sort({
    birthdate:-1,
    name:1
    })

Deleting Indexes

Too many indexes can affect performance. But first you need to ensure that an index is not being used, because deleting that index will greatly impact the performance of that query. You can delete any index except for _id. You can hide an index instead of deleting it to test the effect of removing the index from the database. This can be done with the db.collection.hideIndex(<index>) command.