Compound Indexes with Sorting

I have a question. I have a fake index { a: 1, b: 1, c: 1, d: 1}. I understand that if I do

db.col.find( { a: __, b: {$lt: __ } ).sort( { b: 1} )
this query will take advantage of the index because while I do not look for equality for field b, I am using it to sort. I can confirm with the following:
28%20PM

However, I assume the following query doesn’t utilize the index for both filtering and sorting based on the executionStats, but I don’t understand why. Can someone explain why?

db.col.find( { a: __, b: __ ).sort( { b: 1} )

@Ethan_87663

Since your index already has ‘b’ sorted, why would it need to sort again? Once the documents have been extracted, they’re already in the required order. Am I missing something in your question here?

1 Like

@DHz Totally, agree with your answer. Just wanted to confirm additional thing based on your answer.
Since ‘B’ is specific to one unique value in query predicate, the resultset is ordered and doesn’t need sorting. Consider following example index : {last_name:1, first_name: 1, ssn: 1}

following queries doesn’t show anything regarding “SORT_” in execution plan, meaning that none of the following queries needed memory sort.

  1. find({“last_name”: “Johnson”, “first_name”: “Mary”}).sort({“last_name”: 1})
  2. find({“last_name”: “Johnson”, “first_name”: “Mary”}).sort({“ssn”: 1})
  3. find({“last_name”: “Johnson”, “first_name”: “Mary”}).sort({“last_name”: -1})
  4. find({“last_name”: “Johnson”, “first_name”: “Mary”}).sort({“ssn”: -1})

From my understanding, First 3 queries result set are already in requested order (as per .sort predicates) once after fetched. So MongoDB either ignores .sort() condition OR performs index sort SILENTLY (as it doesn’t shows up explain plan)
But fourth query needs sorting (in desc order). For which MongoDB performs index sort SILENTLY (NO SORT_ relevant details in explain either).

Now for below query in question DOES have to perform MEMORY SORT!! Why?!
5. find({“last_name”: “Johnson”, “first_name”: “Mary”}).sort({“first_name”: 1})

Why SMART query planner performs additional MEMORY SORT (which consumes memory) for query 5 which it skips for query 3. What is the analogy for query 3 and 5 are being handled differently?

Hi @Siddharth_48955,

I think @DHz is on hiatus from here.

First of all, in all cases, the query predicate/filter will be reordered like below in order to use the index prefix:
find({first_name: "Mary", last_name: "Johnson"}).

So let’s look at each one of your queries.

  • An in-memory sort is performed because the index prefixes used in the query predicate/filter are equality conditions and the sort field is not ssn.
  • The sort on ssn utilises the index because the query predicates are index prefixes + use equality conditions, and ssn follows on from last_name. The index scan direction will be forward for (2) and backward for (4).
  • An index scan is performed on the sort field. A forward index scan on the query predicate satisfies the sort condition.
1 Like