Compound Index | MongoDB Indexing Tutorial

Index Performance | MongoDB Indexing Tutorial

Indexing Types | MongoDB Indexing Tutorial

Unique Index | MongoDB Indexing Tutorial

List Indexes | MongoDB Indexing Tutorial

Create Index | MongoDB Indexing Tutorial

Preface: What is an index?

An index stores a portion of a collection's data in a sorted B-Tree for faster queries.

An index improves the performance of database queries because it can avoid table scans when looking for documents.

Without an index, Mongo must scan the entire collection to find the data being asked for in the query.

While an index can improve performance, it ultimately increases the size of your database as information is being stored twice.

Additionally, there is a "book keeping" cost to managing inserts, updates, and deletes since the index must be kept up to date with the actual collection.

Correctly indexing your MongoDB requires a good understanding of your application and what fields will be most frequently queried against.

Indexing Types in MongoDB

Let's say your document structure looks like this...

{
  _id: ObjectID(...),
  email: "sam.erickson@gmail.com",
  name: "Sam Erickson",
  address: {city:"New York", state:"NY", zip:10001},
  roles: ["admin", "user", "author", "originator"],
  age: 42,
  location: {
    type: "Point",
    coordinates: [-73.856077, 40.848447]
  },
  currLoc: [-73,40],
  createdAt: ISODate("2016-11-29T00:53:02.000Z")
}

Single field index

A single field index creates an index on a single field in the collection...

db.collection.createIndex({name:1})

This index will make the following queries much faster...

db.collection.find({name:"Erica"})
db.collection.find({email:"ted@gmail.com", name:"Sam"})

You can also add a single field index on embedded documents like this...

db.createIndex({"address"})
db.createIndex({"address.city"})

When should you use a single field index in MongoDB?

Using a single field index is a good idea when you are frequently querying data against a single field.

For more information on creating a single field index, check out Create Index | MongoDB.

Compound index

A compound index creates an index on multiple fields in a single collection...

db.collection.createIndex({name:1, email:1, age:-1})

This index will make the following queries much faster...

db.collection.find({name:"Erica"})
db.collection.find({name:"Ericoa", email:"ted@gmail.com", age:30})
db.collection.find({email:"ted@gmail.com", name:"Sarah"})
db.collection.find({name:"Sam", age:{$lte:30}}).sort({emai:1})

However, this index WILL NOT help with the following queries...

db.collection.find({email:"ted@gmail.com"})
db.collection.find({email:"ted@gmail.com", age:30})

The reason being is order matters with compound indexes. More specifically, prefixes dictate what combinations of fields will work.

Confused? Check out our deep dive on compound indexes.

When should you use a compound index in MongoDB?

Use a compound index when you frequently query based on multiple fields in your collection.

Multikey index

A multikey index creates an index key for each element in an array field....

db.collection.createIndex({roles:1})

You will notice from the sample data that the roles field is an array.

Since we are creating an index on a field containing array values, MongoDB will automatically create this multikey index for us.

Some limitations do exist on multikey indexes, especially when created as compound indexes.

For more information, check out our multikey index example.

When should you use a multikey index in MongoDB?

This is kind of a trick question since multikey indexes are created automatically in MongoDB. Remember that a multikey index is automatically created whenever you index a field containing array values.

Text index

A text index allows you to run legacy text search queries on a collection...

db.collection.createIndex({name:"text"})

While you can only have one text index defined on a collection, this index can cover multiple fields...

db.collection.createIndex({name:"text",email:"text"})

When should you use a text index in MongoDB?

Use a text index when you want to run legacy text based searches on string based fields.

Use a text index when you want to use the $text operator.

For more information, considerations, and examples check out our text index example.

Wildcard index

A wildcard index allows you to create a dynamic index on fields whose values can evolve over time (schemaless)

db.collection.createIndex({"address.$**": 1})

This makes sense if our address field stores data in different ways over time...

{address: {city:"New York", state:"NY", zip:10001}},
{address: "123 my street"},
{address: {city:"San Fransisco", aptNumber:1}},
{address: 34}

When should you use a wildcard index in MongoDB?

Use a wildcard index when you want to index a field that may change it's shape over time.

Please note that a wildcard index should not be considered a lazy shortcut to defining explicit field indexes due to the performance cost it can introduce.

See our creating a wildcard index example for more on this limitation.

2dsphere index

A 2dsphere index allows you to run geospatial type queries in MongoDB...

document.collection.createIndex({location:"2dsphere"})

Note that a 2dsphere index only works on fields that are GeoJSON objects or legacy coordinate pairs.

When should you use a 2dsphere index in MongoDB?

2dsphere indexes only apply to geospatial type queries that calculate geometries on an earth-like sphere.

2d index

Similar to a 2dsphere index, a 2d index supports queries that calculate geometries based on 2 dimensional planes...

document.collection.createIndex({currLoc:"2d"})

The key difference here is that a 2d index supports fields that are legacy coordinate pairs only (unlike 2dsphere index which works with GeoJSON objects AND legacy coordinate pairs.

When should you use a 2d index in MongoDB?

Use a 2d index when you want to perform geospatial queries on a 2 dimensional plane.

Hashed index

A hashed index creates a hashed value for the targeted index field..

db.collection.createIndex({name:"hashed"})

When should you use a hashed index in MongoDB?

Hash indexes mainly apply to a sharded cluster where data needs to be evenly distributed across different nodes.

Index Properties

When creating an index for your collection you can optionally set properties for the index including...

Unique Indexes

A unique index restricts the indexed field to only having unique values. This prevents duplicate entries for the indexed field...

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

By specifying {unique:true}, Mongo will enforce uniqueness when inserting emails into the database.

For more information on unique indexes and how they work, be sure to check out our unique index tutorial.

Partial Indexes

Partial indexes allow you to only index a portion of the collection data based on a filter expression...

db.collection.createIndex(
   { email: 1, name: 1 },
   { partialFilterExpression: { age: { $gt: 30 } } }
)

This creates a compound index but only on the documents having an age value greater than 30.

When should you create a partial index?

Create a partial index when you want to only index a portion of the collection. This can save storage space and reduce the overall performance cost of creating an index in Mongo.

Sparse Indexes

A sparse index omits null and missing values for the provided index field..

db.collection.createIndex({age:1},{sparse:true})

This simply excludes documents not having the age field or documents where the age field is null

When should you create a sparse index?

Create a sparse index when you'll have lots of blank/null values for the indexed field. This will save space and minimize performance costs.

TTL Indexes

A TTL index is a single field index that automatically deletes documents based on a time constraint...

db.collection.createIndex({createdAt:1},{expireAfterSeconds:60000})

This will automatically remove records after 60000 seconds.

When should you create a TTL index?

Create a TTL index when you want to automatically remove documents after a certain amount of time. Keep in mind that a TTL index only works on fields having date values or an array of date values.

Hidden Indexes

A hidden index isn't actually used but can help evaluate impact of dropping an index...

db.collection.createIndex(
   { age: 1 },
   { hidden: true }
);

When should you create a hidden index?

A hidden index makes sense when you want to evaluate performance without affecting the actual operations of your DB.

How do compound indexes work in MongoDB?

Your thoughts?

Trending