The created indexes in the mongodb collection still do not work when sorting a large dataset

Question

My request is below:

db.chats.find({ bid: 'someID' }).sort({start_time: 1}).limit(10).skip(82560).pretty() 

I have indexes on a collection of chats in fields in that order

 { "cid" : 1, "bid" : 1, "start_time" : 1 } 

I try to sort, but when I write a request and check the result of the explanation (), I still get a winning plan as

 { "stage":"SKIP", "skipAmount":82560, "inputStage":{ "stage":"SORT", "sortPattern":{ "start_time":1 }, "limitAmount":82570, "inputStage":{ "stage":"SORT_KEY_GENERATOR", "inputStage":{ "stage":"COLLSCAN", "filter":{ "ID":{ "$eq":"someID" } }, "direction":"forward" } } } } 

I expected that I would not have a sorting stage, since I have indexes created for this collection. Missing indexes will result in the following error

 MongoError: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM [duplicate] 

However, I managed to do the sorting job by increasing the size distribution on the drum from 32 MB to 64 MB, looking for help in properly adding indexes

Answer 1

The order of the fields in the index matters. To sort the query results by a field that is not at the beginning of the index key template, the query must include equality conditions for all prefix keys preceding the sort keys. The cid field is not included in the request and is not used for sorting, so you must leave it. Then you put the bid field first in the index definition, as you use it in the equality condition. After that, start_time is started, which will be used when sorting. Finally, the index should look like this:

 {"bid" : 1, "start_time" : 1} 

See the documentation for more details.