Mongodb compound index with sort on _id unique index

Question

I have a collection with document like:

{
  _id: "",
  reportId: "a",
  accountId: "",
   ....
 }

Now my query pattern is like:

db.saleReport.find({reportId: "e6044e8a", "accountId":{$in: ["a","b"] }).sort({"_id":1});

I have created a compound index with:

db.saleReport.createIndex({reportId:1,accountId:1})

The above index is getting used only in query stage. There is a sort stage added even to sort on _id field.

Should I add _id also as part of index? Isn't the _id field stored in index already to uniquely identify the record?

What did you do in the end? Was the _id index in necessary? – amidzic89 Apr 28 '21 at 08:04 — amidzic89, Apr 28 '21 at 08:04

score 0 · Answer 1 · answered May 08 '21 at 19:27

Isn't the _id field stored in index already to uniquely identify the record?

Yes this will perform sort operation, but Blocking Sort Operation, it means in memory sort operation,

Behaviour of Blocking Sort:

To check if MongoDB must perform a blocking sort, append cursor.explain() to the query and check the explain results. If the query plan contains a SORT stage, then MongoDB must perform a blocking sort operation subject to the 100 megabyte memory limit.

If MongoDB requires using more than 100 megabytes of system memory for the blocking sort operation, MongoDB returns an error unless the query specifies cursor.allowDiskUse() (New in MongoDB 4.4). allowDiskUse() allows MongoDB to use temporary files on disk to store data exceeding the 100 megabyte system memory limit while processing a blocking sort operation.

You can use allowDiskUse in find method from MongoDB 4.4, if you don't want add _id field in your compound index,

db.saleReport
  .find({reportId: "e6044e8a", "accountId":{$in: ["a","b"] })
  .allowDiskUse()
  .sort({"_id":1});

Should I add _id also as part of index?

Why intersection of _id and compound index is not applying?

See Index Intersection and Sort: Index intersection does not apply when the sort() operation requires an index completely separate from the query predicate.

Yes you can definitely add _id field as part of compound index, if you don't want to perform Blocking Sort or in memory sort operation,

db.saleReport.createIndex({reportId:1,accountId:1,_id:1});

Mongodb compound index with sort on _id unique index

1 Answers1