Database Reference
In-Depth Information
This is the optimal
explain
output for this query: the values of
n
and
nscanned
are the
same. But now consider the case where no one index perfectly serves the query. For
example, imagine that you don't have an index on
{stock_symbol:
1,
close:
1}
but
that, instead, you have a separate index on each of those fields. Using the shorthand
getIndexKeys()
to list indexes, you'd see this:
db.values.getIndexKeys()
[ { "_id" : 1 }, { "close" : 1 }, { "stock_symbol" : 1 } ]
Because your query includes both the
stock_symbol
and
close
keys, there's no obvi-
ous index to use. This is where the query optimizer comes in, and the heuristic is
more straightforward than you might imagine. It's based purely on the value of
nscanned
. In other words, the optimizer chooses the index that requires scanning the
least number of index entries. When the query is first run, the optimizer creates a
query plan for each index that might efficiently satisfy the query. The optimizer then
runs each plan in parallel.
14
The plan that finishes with the lowest value for
nscanned
is declared the winner. The optimizer then halts any long-running plans and saves the
winner for future use.
You can see this process in action by issuing your query and running
explain()
.
First, drop the compound index on
{stock_symbol:
1,
close:
1}
and build separate
indexes on each of these keys:
db.values.dropIndex("stock_symbol_1_close_1")
db.values.ensureIndex({stock_symbol: 1})
db.values.ensureIndex({close: 1})
Then pass
true
to the explain method, which will include the list of plans the query
optimizer attempts. You can see the output in listing 7.1.
Listing 7.1
Viewing query plans with
explain(true)
db.values.find({stock_symbol: "GOOG", close: {$gt: 200}}).explain(true)
{
"cursor" : "BtreeCursor stock_symbol_1",
"nscanned" : 894,
"nscannedObjects" : 894,
"n" : 730,
"millis" : 8,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"stock_symbol" : [
[
"GOOG",
"GOOG"
]
]
14
Technically, the plans are interleaved.
