← back to the blog


Equality, Sort, Range - MongoDB index creation planning

Posted on September 5th, 2020 in JavaScript by George

 

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 here:

//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 })

 

The first index is using as a first index prefix, the range field.

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

This is generally a bad idea.

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

This index will have to examine 11.611 documents out of 1.000.000, but because of the range prefix of the index, the keys examined are 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 first prefix.

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.

 

In the last index, we are going to move the range prefix at the end.

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

This way 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.

 

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.