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…