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

3

Hi, I have a follow-up question regarding index direction. In the example above where records would be inserted with “createTime” values increasing in value,
if we assumed that most/all queries contained a “createTime” condition where “createTime” gte a recent (large) time value, do we gain any performance benefits by creating an index with DESCENDING direction “createTime” component? In your example above you recommended a createTime field with ASCENDING direction; I am trying to understand if that was optimizing performance due to the given query being a “less than or equal” range-based-match or any other reason. Thank you for any insights!