Database Reference
In-Depth Information
Satisfy all fields with useful indexing constraints—attempt to use indexes for the
fields in the query selector.
2
If the query implies a range or includes a sort, then choose an index where that
last key used can help satisfy the range or sort.
3
If all of these conditions can be met for any one index, then that index will be consid-
ered optimal and will be used. If more than one index qualifies as optimal, then one
of the optimal indexes will be chosen arbitrarily. There's a lesson here: if you can
build optimal indexes for your queries, you make the query optimizer's job a lot eas-
ier. Strive for that if you can.
Let's look at a query that satisfies an index (and the query optimizer) perfectly. Go
back to the stock symbol data set. Now imagine you want to issue the following query,
which fetches all of Google's closing values greater than 200:
db.values.find({stock_symbol: "GOOG", close: {$gt: 200}})
The optimal index for this query includes both keys but places the
close
key last to
allow for the range query:
db.values.ensureIndex({stock_symbol: 1, close: 1})
You'll see that if you run the query, both keys are used, and the index bounds are as
expected:
db.values.find({stock_symbol: "GOOG", close: {$gt: 200}}).explain()
{
"cursor" : "BtreeCursor stock_symbol_1_close_1",
"nscanned" : 730,
"nscannedObjects" : 730,
"n" : 730,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"stock_symbol" : [
[
"GOOG",
"GOOG"
]
],
"close" : [
[
200,
1.7976931348623157e+308
]
]
}
}
>
