Compound index for filtering and sorting question

Dear Mongodb fellas,
I have a question about the use of a compound index for both, filtering and sorting. After watching the M201 chapter’s video many times I still didn’t understand when it works. For example here if I use te following index ({job : 1, employer :1, last_name :1, first_name :1}) . The compound index will be used for both filtering and sorting when :

  • There is a equelity filtering before the sorting. for example this won’t use the compound index (except if the filtered field is the same one that we sort): db.people.find({ “job”: { $gt: “J” } }).sort({ “employer”: -1 })

  • When it respects order of the prefexes, so for example this won’t work : db.people.find({ “job”: “devops” }).sort({ “first_name”: -1 })

But then I came accross the chapter’s lab and I found this for the following index : { “first_name”: 1, “address.state”: -1, “”: -1, “ssn”: 1 }

I don’t understand why this uses the compound index, normally it shouldn’t use any index at all. Could you please help me with this issue.

Best regards,

Because they both use equality. A EQUALS B AND C EQUALS D is equivalent to C EQUAL D D AND A EQUALS B. So the query planner can reorder them to match the index order.

1 Like

@rida_kejji this is due to the “Equality-Sort-Range” rule of compound index key ordering. I have written about this in detail at Optimizing MongoDB Compound Indexes - The "Equality - Sort - Range" (ESR) Rule | ALEX BEVILACQUA, but to summarize:

Operation: find({ "address.state": "X", "first_name": "Y" }).sort({ "": -1 })
Index: { "first_name": 1, "address.state": -1, "": -1, "ssn": 1 }

Both address.state and first_name are performing (E)quality ($eq) comparisons, and they’re the first two fields (from left to right) in the index definition. Next, the operation is (S)orting on the field, which is the third field (from left to right) in the index definition.

Even though the ssn field is not represented in this operation, all other fields appear (from left to right) and follow the “E-S-R Ordering Rule” and can thus leverage this index.

1 Like

Hello Steeve, thank you for your answer.

I have got a question though, does that mean that if we declare a compound index and only use equality filters, the order won’t matter.
Like for example for an index : ({job : 1, employer :1, last_name :1, first_name :1})
if I look for db.people.find({last_name : “kejji”, employer :“Apple”, job :“devops”, first_name :“rida”}). Will this use the compound index. According to what you said earlier, it looks like a AND logical operator. could you confirm that ?

Thank you,
Best regards,

Yes, the index will be used.

1 Like

See for yourself in mongosh.

db.c.insertOne( {last_name : "kejji", employer :"Apple", job :"devops", first_name :"rida"} )
db.c.createIndex( {job : 1, employer :1, last_name :1, first_name :1} )
db.c.find( {last_name : "kejji", employer :"Apple", job :"devops", first_name :"rida"} ).explain().queryPlanner.winningPlan.inputStage
1 Like

Ah awesome, so its indeed an AND logical operator when using Equality !


This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.