Why is this SORT stage here? Shouldn't it use the index instead?

[Kevin_Rathgeber]

I have an index that looks like this:

{a: 1, b: 1}

And my query looks something like this:

db.myCollection.find({a: {$regex : "^S"}}).sort({b:1})

When I do an “explain” on this plan, I’m seeing a SORT stage which means it’s using CPU and memory to do the sorting rather than using the order of the index. Is there a way to make this work?

[steevej (Steeve Juneau)]

The index {a:1,b:1} supports sorts on {a:1,b:1}, {a:1,b:-1}, {a:-1,b:1} and {a:-1,b-1}.

For example, in documents:

_id:0,a:S0,b:20
_id:1,a:S1,b:10
_id:2,a:S0,b15

your query and sort wants

_id:1,a:S1,b:10
_id:0,a:S0,b:20

so while an IXSCAN can determine that _id:0 and _id:1 are in the result set, a SORT must determine the final order.

[steevej (Steeve Juneau)]

@Kevin_Rathgeber, was my post help you understand better. If it did, please mark my post as the solution. This will help keep this post useful and efficient. It is simply courtesy.