Created indexes on a mongodb collection, still fails while sorting a large data set

Question

My Query below:

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

I have indexes on chats collection on the fields in this order

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

I am trying to perform sort, but when I write a query and check the result of explain(), I still get the winningPlan 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 was expecting not to have a sort stage in the winning plan as I have indexes created for that collection. Having no indexes will result into the following error

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

However I managed to make the sort work by increasing the size allocation on ram from 32mb to 64mb, looking for help in adding indexes properly

What do documents in the collection look like? What indexes do you have? What is the query? — Andriy Simonov
– Andriy Simonov, Commented Feb 5, 2018 at 8:39
@AndriySimonov , I have updated the question, kindly have a look again. — AnoopGoudar
– AnoopGoudar, Commented Feb 6, 2018 at 7:53

Andriy Simonov · Accepted Answer · 2018-02-07 08:28:47Z

2

The order of fields in an index matters. To sort query results by a field that is not at the start of the index key pattern the query must include equality conditions on all the prefix keys that precede the sort keys. The cid field is not in the query nor used for sorting, so you must leave it out. Then you put the bid field first in the index definition as you use it in the equality condition. The start_time goes after that to be used in sorting. Finally, the index must look like this:

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

See the documentation for further reference.

edited Feb 7, 2018 at 8:28

answered Feb 6, 2018 at 15:25

Andriy Simonov

1,3081 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnoopGoudar Over a year ago

thank you @andriy, I will have try the way you suggested.But can I have indexing on cid at the end, because I may use cid in other queries. May be like this {"bid" : 1, "start_time" : 1, "cid": 1}

Andriy Simonov Over a year ago

@AnoopGoudar You probably can, however whether this index is going to be used or not depends greatly on those other queries.

Collectives™ on Stack Overflow

Created indexes on a mongodb collection, still fails while sorting a large data set

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related