MongoDB – Part 3 – Indexes

posted in Document Databases, MongoDB, NoSQL on by

It has been a while since my last post on MongoDB, but I’m back and looking to finish off this series over the next 6-8 weeks (Edit: 6-8 months). Anyway, in this article I’m going to be covering all of the different types of indexes you can use in MongoDB

Most of these you’ll have heard of before, providing you’ve used almost any other DBMS. I’ll be providing examples of how to create each index, when you would want to use them and at the end I’ll throw together a few must know tips.

The everyday index

A standard index is the most common kind of index. They’re incredibly easy to create and use. Here are some of their properties:

  1. A standard index is created on a single key on all documents.
  2. Creating an index applies to all documents in the collection that contain the key specified.
  3. If a document does not contain the indexed key, the document will not be part of the index.

Creating a standard index

db.user.ensureIndex({ country: 1 })

What the line above will do, is create an index on the country key in the user collection. As with indexes in other DBMS, it’s better to create an index on keys which have a higher cardinality (lots of unique values). You also want to make sure, you’re indexing keys which you most frequently query on. Indexes also work when using modifiers. Some modifiers however do not utilise indexes very well, I’ll cover those later.

Standard indexes are automatically used when running a query on MongoDB. The majority of modifiers will make use of indexes.

Compound Index

Compound indexes are another very common form of index. They’re created just like standard indexes. The only difference is that compound indexes are multi-key. This is useful when you frequently query on more than one key at a time. MongoDB will only make use of indexes when they’re defined in the same order they’re used in. Compound indexes are also implicit, which means if you have a compound index over three keys, in the order: birth_year, country, name. MongoDB will make use of the index when querying on all three keys, or birth_year and country, or just birth_year. This is exactly the same as in MySQL. Compound indexes are used automatically just like standard indexes.

Creating a compound index

db.users.ensureIndex({ birth_year: 1, country: 1, name: 1 })

Covered Index

Covered indexes aren’t really a type of index, but rather they’re compound indexes, which don’t require lookups to the actual collection, this happens when the index contains all of the data required. Covered indexes are used automatically.

Unique Index

These are similar to standard indexes, the only difference being that each record in the index must be unique. The null value must also be unique.

Creating a unique index

db.user.ensureIndex({ country: 1 }, { unique: true })

Unique Compound Index

By now you can probably guess what these are. If not just imagine a compound index where each collection of keys must be unique.

Creating a unique compound index

db.user.ensureIndex({ country: 1, name: 1 }, { unique: true })

In the above example, each pair of country and name keys, must be unique.

Sparse Index

These indexes are very useful to use alongside unique indexes. They allow you to create unique indexes, but with an unlimited number of null values. Sparse indexes also not return null values when running a “Not Equal” query like:

db.user.find({ name: { $ne: 'some-name' } })

Creating a sparse index

db.user.ensureIndex({ country: 1 }, { sparse: true })

Creating a unique and sparse index

db.user.ensureIndex({ country: 1 }, { unique: true, sparse: true })

TTL – Time to live Index

TTL indexes can be on single keys or compound keys, they can also be unique and sparse. The additional functionality they provide, is providing a life span in seconds for all documents.

Creating a TTL index

db.log.ensureIndex({ "lastUpdated": 1}, { expireAfterSeconds: 60*60*24 })

What this will do, is delete all documents from the log collection, once their lastUpdated value is more than 24 hours (60*60*24 seconds) in the past. You can probably imagine, just how useful this is in game development.

Full Text Index

These can be used to search for data inside a text value. Full text searches have to be enabled on each MongoDB node, before they can be used. This can be done by executing the below in a Mongo console:

db.adminCommand({ "setParameter": 1, "textSearchEnabled": true})

Create compound full text index over 2 keys.

db.user.ensureIndex({ job_description: "text", personal_profile: "text" })

Create a full text index on every string field in the document, this will also search arrays which contain strings.

db.user.ensureIndex({ $**: "text" })

Using a full text index – Search for any of the 3 words provided.

db.runCommand({ text: "use", search: "some search words" })

Geospatial – 2dsphere

These indexes can be used to search for records on a grid or a sphere using GeoJSON.

Create a 2dsphere index

db.places.ensureIndex({"tile" : "2dsphere"})

# The default grid is -180 to 180, this can be adjusted as below
db.places.ensureIndex({"light-years" : "2dsphere"}, {"min" : -1000, "max" : 1000})

Using a 2dsphere index 

# Find objects near a tile
db.places.find({"tile" : {"$near" : [20, 21]}})

# Find objects within a box radius
db.places.find({"tile" : {"$within" : {"$box" : [[10, 20], [15, 30]]}}})

# Find object in a radius of 5 from a center point
db.places.find({"tile" : {"$within" : {"$center" : [[10, 20], 5]}}})

# Find objects within a polygon
db.places.find({"tile" : {"$within" : {"$polygon" : [[0, 1], [1, 2], [2, 3]]}}})

Example 2dsphere document.

When using 2dsphere indexes, the key that you make the index, must also contain an object with two keys called type & coordinates. The type attribute defines the type of vector e.g. point, line, shape. The coordinates attributes states the points on the grid, where the object is located.

{ tile: {
    type: "MultiPoint",
    coordinates: [
       [ 1, 1 ],
       [ 1, 5 ],
       [ 5, 5 ],
       [ 5, 1 ]
    ]
  }
}

Hashed Index

Hashed indexes are different from normal indexes in that, instead of indexing the values. The hashed index will store a hash of the values in the index. Doing this helps to evenly distribute chunks across a shard. You can think of chunks as a collection of documents. Hashed indexes cannot be sparse or compound.

Create a hashed index

db.coll.ensureIndex({"country" : “hashed”})

Useful index commands

# Create an index in the background. 
# Creating indexes can be slow and puts a temporary lock on the table, which will prevents writes.
# To counter this, you can create an index in the background, the process will
# occasionally yield to allow writes to occur.
db.items.ensureIndex({ "amount": 1 }, { "background": true })

# Fetch a list of all on a collection and their names
# Index names are by default automatically generated
db.items.getIndexes()

# Drop an index by its name
db.items.dropIndex("indexName")

Modifier that support indexes badly

As I mentioned above, some modifiers do not play nicely with indexes, these are described below:

  1. $where – Cannot use indexes
  2. $exists, $ne, $not – Little benefit to using indexes

This may change in newer versions of MongoDB.

Index Tips

  1. Indexes should be created on one secondary at a time, by putting it in standalone mode. This will prevent you from locking up your primary node.
  2. Once all secondaries have been updated, the primary should be updated, this can be done two ways:
    1. Taking it into standalone mode and adding the index.
    2. Create the index, in the background.
      1. db.users.ensureIndex({ name: 1 }, { background: true })

Conclusion

That pretty much gives a brief overview of all the different types of indexes available in MongoDB, as always the docs are fantastic if you want to learn more.

3 Love This

Leave a Reply

Your email address will not be published.