Memory sort even the sort key is part of an index

[Ahmed_Naser1 (Ahmed Naser)]

i have created a regular index with two fields status and createdAt and i want to sort my result with createdAt but it gives me memory sort, why not sorting using index?

[turivishal (Vishal Turi)]

Hello @Ahmed_Naser1, Welcome back to the MongoDB community forum,

You need to understand the ESR (Equality, Sort, Range) rule of indexing, this is really the best technique to satisfy the compound index, refer to the documentation for more details:

The ESR (Equality, Sort, Range) Rule — MongoDB Manual

In your case:

• Equality: no equality condition available in your query
• Sort: { createdAt: 1 }
• Range: { status: { $ne: "DELETED" } }

On the basis of the ESR (Equality, Sort, Range) rule, your index should be as below and it will definitely satisfy the query’s sort operation.

.createIndex({ createdAt: 1, status: 1 })