Description
My collection has 1 million data.
I am performing this query to retrieve
x > 70000 or x < 10000
In case 1, the pid (player id) of the collection isn't indexed.
a) if i query with {$or:[{pid:{$gt:700000}},{pid:{$lt:100000}}]},
it took around 750ms
b) if i query with {$or:[{pid:{$lt:100000}},{pid:{$gt:700000}}]}, (simply change the order of expression)
it took around 800ms
Everything is still ok.
BUT in case 2, i indexed pid, which supposed to provide a faster query. However,
a) if i query with {$or:[{pid:{$gt:700000}},{pid:{$lt:100000}}]},
it took around 980ms
b) WORSE, if i query with {$or:[{pid:{$lt:100000}},{pid:{$gt:700000}}]},
it took around 1600ms!!
I have 2 questions here:
1. Why the indexed collection takes longer?
2. Why different expressions could have such a big difference in the time of query? ( does it have something to do with $or?)