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.

crunk1
  • 2,560
  • 1
  • 26
  • 32

1 Answers1

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!

Yahya
  • 3,386
  • 3
  • 22
  • 40
  • Interesting. The mongo documentation lists the $lt, $lte, $gt, $gte in their examples, so it seems they consider them "equality conditions". – crunk1 Jul 16 '20 at 09:46
  • Specifically, `db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } )` prefix={ a: 1, b: 1 } – crunk1 Jul 16 '20 at 10:02
  • 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](https://docs.mongodb.com/manual/reference/method/db.collection.explain/index.html#db-collection-explain). If it uses the index for sorting, there will not be a sort or sort key generator stage. – Joe Jul 16 '20 at 10:28
  • $lt, $lte, $gt, $gte are all range operators. They are under Comparison Query Operator, not essentially Equality. – Yahya Jul 16 '20 at 11:02
  • 1
    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! – crunk1 Jul 16 '20 at 23:05
  • Hmm, but now I'm getting the sort_key_generator even with this index... weird. – crunk1 Jul 17 '20 at 01:00
  • It might be that your data is not wide enough to be able to see the difference in performance between two indexes. Remove the other index and then try again. – Yahya Jul 17 '20 at 07:25