MongoDB Indexing – Compound Indexes for Different Sorts

mongodbnosql

I'm searching on a field and I want to sort on 3 different fields (viewcount,rating,time) separately. Am I correct in assuming that I need to do three different compound indexes {searchField :1, viewcount:1}, {searchField :1, rating:1} and {searchField :1, time:1} ?

Also one query could search with the searchField then do a range query and a sort on the time field, the last index would be the correct one too in this situation right ?

Best Answer

Your assumption is correct with one comment about sort order. Make sure your query sort order matches your index sort order otherwise index will not be used. Read more details here.

Consider a collection events that contains documents with the fields username and date. Applications can issue queries that return results sorted first by ascending username values and then by descending (i.e. more recent to last) date values, such as:

db.events.find().sort( { username: 1, date: -1 } )

or queries that return results sorted first by descending username values and then by ascending date values, such as:

db.events.find().sort( { username: -1, date: 1 } )

The following index can support both these sort operations:

db.events.createIndex( { "username" : 1, "date" : -1 } )

However, the above index cannot support sorting by ascending username values and then by ascending date values, such as the following:

db.events.find().sort( { username: 1, date: 1 } )
Related Question