0

MongoDB documentation

https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/#sort-on-multiple-fields:

For a query to use a compound index for a sort, the specified sort direction for all keys in the cursor.sort() document must match the index key pattern or match the inverse of the index key pattern. For example, an index key pattern { a: 1, b: -1 } can support a sort on { a: 1, b: -1 } and { a: -1, b: 1 } but not on { a: -1, b: -1 } or {a: 1, b: 1}.

https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/#sort-and-non-prefix-subset-of-an-index:

An index can support sort operations on a non-prefix subset of the index key pattern. To do so, the query must include equality conditions on all the prefix keys that precede the sort keys.

Question

OK, so given an index {a: 1, b: 1, c: 1, d: 1}, the first quote says I can do sorts {a: 1, b: 1, c: 1, d: 1} and its inverse {a: -1, b: -1, c: -1, d: -1}. Awesome. And the second quote says I can use the index for querying with index prefixes and sorting by an index "suffix" (if I can coin that term), e.g. db.Foo.find({a: 52, b: {$lt: 5}, c: {$gte: 12}}).sort({d: 1}). Also awesome.

My question then is, will db.Foo.find({a: 52, b: {$lt: 5}, c: {$gte: 12}}).sort({d: -1}) (note the descending sort on d) match the index {a: 1, b: 1, c: 1, d: 1}? Will it invert the index behind the scenes and use {a: -1, b: -1, c: -1, d: -1}? Near as I can tell, mongo's documentation doesn't cover this case.

Improve this question

1 Answer 1

Reset to default
2

The stages are; Index Scan to filter documents and then in-memory sort. So the answer is, it is not leveraging the index for sorting in your scenario.

But if you have an index like:

{a: 1, d: 1, b: 1, c: 1}

Which is Equality, then Sort and then Range. It will leverage the index whichever way your queries are and whichever direction your sort is on d.

Behind the scenes, these are B+ trees!

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Interesting. The mongo documentation lists the $lt, $lte, $gt, $gte in their examples, so it seems they consider them "equality conditions".
Specifically, db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } ) prefix={ a: 1, b: 1 }
In that example, the sort in on b, and all keys that appear earlier in the index use an equality match. $lt,$lte, $gt, and $gte are not equality match, so the example in the question will not use the index. To prove this to your self, create a collection with a few dozen documents containing those fields and some random values, create that index, and then run the query using explain. If it uses the index for sorting, there will not be a sort or sort key generator stage.
$lt, $lte, $gt, $gte are all range operators. They are under Comparison Query Operator, not essentially Equality.
Hey Yahya, I did try out {a: 1, d: 1, b: 1, c: 1} and you are correct. And in answer to my question, that index will service either d: 1 or d: -1, or in other words, inverting the non-prefix subset index will still utilize the index :) Thanks for your explanations!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.