Sorting Mongo records on non unique created date time field

Question

I am sorting on created field which is in the form of 2022-03-26T03:56:13.176+00:00 and is a representation of a java.time.LocalDateTime.

Sorting by only created is not consistent as the field is not unique, due to batch operations that run quickly enough to result in duplicates.

I've added a second sort, on _id, which is ObjectId.

It seems the second sort adds quite a bit of time to the query, more so than the first, which is odd to me.

Why does it more than double response time, and is there a more preferred way to ensure the order?

Using MongoTemplate, I sort like this:

query.with(Sort.by(Sort.Order.desc("createdOn"), Sort.Order.desc("_id")));

Answer 1

If your use case is always to sort in descending order on both fileds it is best to create the compound index in the expected sort order as follow:

 db.collection.createIndex({ createdOn:-1,_id:-1 })

But in general the default _id field is containing the document insertion date and it is unique accross mongodb process so you may just sort based on _id , you most porbably don't need to sort additionally on createdOn date ...