Stage and inputStage inside of winningPlan for Indexing

Can anyone tell me which way to create index gets best performance for this query ?

db.movies
.find (  {  country: 'India' ,  ticketPrice: { $lt: 15 }  } )
.sort( { watched: -1 } )
1. { country: 1, ticketPrice: 1, watched: 1 }
2. { watched: 1, country: 1, ticketPrice: 1 }
3. { watched: 1, ticketPrice: 1, country: 1 }
4. { country: 1, watched: 1, ticketPrice: 1 }
5. { ticketPrice: 1, watched: 1, country: 1 }

I got this winningPlan by executing the above query with explain().

see # 1, 4, 5 shows FETCH => SORT => IXSCAN
and #2, 4 shows FETCH => IXSCAN

Can anyone tell me what is the most performant, and why?

also, if winningPlan shows SORT at stage or InputStage, does it mean the query used index for sorting ?

Hope someone gets me clarifying them…

According to the following links index { country:1 , watched:1 , ticketPrice:1 } should perform better as it follows the ESR rule.

2 Likes

Thnx.
you got exactly what I need !!

1 Like

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