What is the ESR guideline for compound indexes, and why does the sort field go before range?

Short answer

Equality, then Sort, then Range. After a range predicate the index is no longer in the sort order you need, so a sort field placed after a range often cannot be satisfied by the index.

Why?

Equality keys narrow to a contiguous slice. Within that slice, a sort key can walk in order. A range like createdAt: { $lt: ... } already walks a spread of keys; asking the same index to also provide a different sort usually means an extra SORT stage. ESR is a guideline, not a law — explain() tells you whether it worked.

Interview tip

Draw the three letters and apply them to the query you were given. Empty theory is weaker than one worked example.

Common mistake

Memorising ESR without being able to order the keys of a real find().sort().