How can sorting be optimized in a MongoDB query?

Question

MongoDB

Good day. There was a problem with the speed of query execution to MongoDB. The request looks like this:

mongoose.model('mail')
.find({
        $or: [
                {sender: user_id, recipient: owner_id, mType: 0, deleted: false},
                {sender: owner_id, recipient: user_id, mType: 1, deleted: false, read: {$ne: 4}, action: {$ne: 'some_type'}}
        ]
})
.populate('sender', 'nickname photos.1 birth online status role')
.populate('recipient', 'nickname photos.1 birth online status')
.sort({date: -1})
.skip((page - 1) * mailsPerPage)
.limit(mailsPerPage)

Sorting in the query greatly increases the processing time. Prompt, how it is possible to optimize request that sorting fulfilled faster?

Answer 1

In a good way, the filter and sort should match the index.
If you need maximum speed, then you need to simplify the query, get rid of $ or and additional. conditions, for example, do this:
1) save the ready sender+recipient value in the document, which passes the condition, for example: { _search1: {user_id1: user_id1, user_id2: user_id2} } (write user ids in ascending order), fill this array if the conditions are met with mType, deleted, read, action.
2) Make an index: db.mail.ensureIndex({_search1: 1, "date" : -1 })
As a result, the query will return the result quickly and taking into account all conditions:
If you don't want to sort the identifiers, then you can make it an array:
{ _search:[sender, recipient] }, and the query

collection.find({ _search:{$all: [sender, recipient]} }).sort({date: -1})

, for this case perhaps the following index would be better: db.mail.ensureIndex({ "date" : -1, _search1: 1 })

Answer 2

Put the index on the field by which sorting:
Please note that the index must have the same sorting, i.e. if in your example descending (-1), then the index should be descending.