Performance impact of an ascending or a descending index during CUD

Working with Data
#1

In a mongo collection, createTime and lastUpdateTime are defined as timestamps and keeps increasing. User might query frequently about the latest updated value. Given the condition this collection might contain 100 millions of data, index shall be created.

Sample documents:

{ _id："1", a: "xx", b: "yy", createTime:ISODate("2022-05-16T06:07:47.280Z"), lastUpdateTime:ISODate("2022-05-16T06:07:47.280Z"), v: ["v1"] }
 { _id："2", a: "xx", b: "yy", createTime:ISODate("2022-05-16T06:07:47.280Z"), lastUpdateTime:ISODate("2022-05-17T07:07:47.280Z"), v: ["v1", "v2"] }

Typical query pattern:

 db["data"].find({a: "xx", "b": "yy", "createTime": { "$lte":ISODate("2022-05-16T06:07:47.280Z") }}）.sort({lastUpdateTime :-1}).limit(1)

There’re two options about the index creation based on ESR rule.

  1. CreateIndex({ a: 1, b: 1, lastUpdateTime :1, createTime: 1})
  2. CreateIndex({ a: 1, b: 1, lastUpdateTime :-1, createTime: 1}).

Both index can help this query. I don’t assume there’re obvious difference while I doubt there’re different performance impact during insert/update/delete. Since timestamp is increasing by nature, I guess the “asc” index should be created. But not sure how to verify it.

#2

Hi @Yinhua_Zhao ,

Actually if the sort is usually desc like the following:

sort({lastUpdateTime :-1})

I suggest to create

CreateIndex({ a: 1, b: 1, lastUpdateTime :-1, createTime: 1}).

This is a more appropriate query shape for the index an should result in better scanning.

The write maintenance overhead on performance would be the same with -1 or 1…

Thanks
Pavel