Equality, Sort, Range - MongoDB index creation

This post is an example of why in most of the queries that include some sort of equality, range, and sorting, the way you are building your indexes matters.

In this post, we are looking at an example of a database with a collection of around 1.000.000 documents.

The query is based on three fields, the first one is a range, the second one equality, and the third one is used for sorting.

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

We are going to create 3 different indexes and then we will discuss why one approach is better than the other.

The indexes are created below:

  //Bad performance
  db.restaurants.createIndex({"address.zipcode": 1,"cuisine": 1,"stars": 1})

  //Mediocre performance (memory sort)
  db.restaurants.createIndex({ "cuisine": 1, "address.zipcode": 1, "stars": 1 })

  //Best performance (index sort)
  db.restaurants.createIndex({ "cuisine": 1, "stars": 1, "address.zipcode": 1 })

Let's discuss each of them, one by one.

The first example has as the index prefix the field used for a range query.

  //Bad performance
  db.restaurants.createIndex({"address.zipcode": 1,"cuisine": 1,"stars": 1})

This is generally a bad idea. This index will have to examine 11.611 documents out of 1.000.000, but because if this approach for creating indexes, the number of keys examined is 95.988 and this is obviously not good enough. Also, this index will have to do an in-memory sort, for the same reason.

• 279ms query time.

For the second index we will change the position of "cuisine" field as the prefix index.

  db.restaurants.createIndex({ "cuisine": 1, "address.zipcode": 1, "stars": 1 })

This index is better because now will examine 11.611 documents and the keys examined are exactly 11.611 but the sort is still done in memory rather than taking advantage of the index.

• 90ms query time. Quite a significant improvement.

For the last example we are going to move the range prefix at the end.

  db.restaurants.createIndex({ "cuisine": 1, "stars": 1, "address.zipcode": 1 })

Now we can take advantage of the index sorting and therefore improve the performance by eliminating the in-memory sort stage.

• 43ms

As you can see, this is way better.

Keep in mind that the order you are creating your indexes may have a conspicuous impact in the performance of your queries and in general Equality, Sort, Range is the best order to go for. It is a good idea to use Explain to get a better understanding of your query.

The benchmark's values are relative to my environment but in other systems the performance difference should be more or less the same.

Thank you.