How to use $hint in MongoDB aggregation query?

2019 answer

From MongoDB version 3.6

From the documentation, you can add hint with aggregation in the following manner:

db.collection.aggregate(pipeline, {hint: "index_name"})

If you want to see explain just add explain as you would without hint

Index definition can be very subjective, and not something you just idly say "index this stuff" and then hope for the best. It actually requires some thought about the search process to which it applies.

Your query here appears to be made up of these main elements, which are mostly the "Account" and "Lifetime" values. Sure there are other things in there like the "VisitTime" notably, but taking the old library and card index analogy then think about the process.

So when you walk through the library door you are presented with two card index systems:

1. Contains the books in the libary by the date they were authored, allowing you to get a selection of the cards pointing to the books based on the date

2. Contains the names of the authors of the books and there locations in the library.

Now considering that you know you want to look for books from an author written in the last 10 years, then which index system do you pick? So do you look through the dates of 10 years and look for the author contained within? Or do you rather first look up the author, and then narrow down to which books have been written in the last 10 years?

Chances are that the last 10 years has a lot more content that just that from a single author. Therefore 2 is the better choice because once you have all books for that author, then going through the cards to find those within 10 years should be a much smaller task.

This is why the order of keys in an index is important to the query patterns you are using. Clearly "Account" should be the thing that narrows the selection the most and then other details to help further narrow that down.

Anything that puts something like a "VisitTime" before that, means you need to sift through all of the things you likely don't want within that period before you actually get to the things you need.

Ordering is important, and you need to always consider that with index design.