Search time with index> no index
I have one set of "numbers" with a 200000 document object with {number: i} i = 1 to 200000.
Without the $ gt index: 10000 gives nscanned 200000 and 115ms. With an index to the number $ gt: 10000, nscanned 189999 and 355 ms are issued.
Why more time with indexing?
> db.numbers.find({number: {$gt: 10000}}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 200000,
"nscannedObjects" : 200000,
"n" : 189999,
"millis" : 115,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
}
}
> db.numbers.ensureIndex({number: 1})
> db.numbers.find({number: {$gt: 10000}}).explain()
{
"cursor" : "BtreeCursor number_1",
"nscanned" : 189999,
"nscannedObjects" : 189999,
"n" : 189999,
"millis" : 355,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"number" : [
[
10000,
1.7976931348623157e+308
]
]
}
}
source to share
The index doesn't help in this case, because your corresponding result set consists of almost the entire collection. This means it has to load into RAM and traverse most of the index, as well as load into RAM and move the documents themselves.
Without an index, it will just do a table scan, check each document, and return if matched.
In such cases, when the query returns nearly the entire collection, the index may not be practical.
Adding .limit () will speed up the request. You can also force the query optimizer not to use the index with .hint ():
db.collection.find().hint({$natural:1})
You can also force the query to provide result values directly from the index itself, restricting the selected fields to only those that you indexed. This avoids the need to download any documents after the index scan.
Try this and see if the output shows an explanation "indexOnly":true
db.numbers.find({number: {$gt: 10000}}, {number:1}).explain()
Details here:
source to share