Why does Mongo hint make a query run up to 10 times faster?

Question

If I run a mongo query from the shell with explain(), get the name of the index used and then run the same query again, but with hint() specifying the same index to be used - "millis" field from explain plan is decreased significantly

for example

no hint provided:

>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).explain();  {     "cursor" : "BtreeCursor my_super_index",     "nscanned" : 599,     "nscannedObjects" : 587,     "n" : 3,     "millis" : 24,     "nYields" : 0,     "nChunkSkips" : 0,     "isMultiKey" : true,     "indexOnly" : false,     "indexBounds" : { ... } }  

hint provided:

>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).hint("my_super_index").explain();  {     "cursor" : "BtreeCursor my_super_index",     "nscanned" : 599,     "nscannedObjects" : 587,     "n" : 3,     "millis" : 2,     "nYields" : 0,     "nChunkSkips" : 0,     "isMultiKey" : true,     "indexOnly" : false,     "indexBounds" : { ... } }  

The only difference is "millis" field

Does anyone know why is that?

UPDATE: "Selecting which index to use" doesn't explain it, because mongo, as far as I know, selects index for each X (100?) runs, so it should be as fast as with hint next (X-1) runs

Answer 1

Mongo uses an algorithm to determine which index to be used when no hint is provided and then caches the index used for the similar query for next 1000 calls

But whenever you explain a mongo query it will always run the index selection algorithm, thus the explain() with hint will always take less time when compared with explain() without hint.

Similar question was answered here Understanding mongo db explain

Answer 2

Mongo did the same search both times as you can see from the number of scanned objects. Also you can see that the used index was the same (take a look at the "cursor" entry), both used already your my_super_index index.

"hint" only tells Mongo to use that specific index which it already automatically did in the first query.

The second search was simple faster because all the data was probably already in the cache.