Database Reference
In-Depth Information
and that you're iterating over the index in reverse order. In the
indexBounds
field,
you see the special values
$maxElement
and
$minElement
. These indicate that the
query spans the entire index. So in this case, the query optimizer walks the rightmost
edge of the B-tree until it reaches the maximum key and then works its way backward.
Since you've specified a limit of 1, the query is complete once the max element is
found. And of course, since the index keeps the entries in order, there's no longer a
need for the manual sort indicated by
scanAndOrder
.
You'll see slightly different output if you use the indexed key in your query selector.
Ta k e a l o o k a t t h e e x p l a i n p l a n f o r a q u e r y s e l e c t i n g c lo s in g v a l ue s g re a t e r t h a n 50 0:
> db.values.find({close: {$gt: 500}}).explain()
{
"cursor" : "BtreeCursor close_1",
"nscanned" : 309,
"nscannedObjects" : 309,
"n" : 309,
"millis" : 5,
"nYields" : 0,
"nChunkSkips" : 0,
"indexBounds" : {
"close" : [
[
500,
1.7976931348623157e+308
]
]
}
}
You're still scanning the same number of documents that you're returning (
n
and
nscanned
are the same), which is ideal. But note the difference in the way the index
boundaries are specified. Instead of the
$maxElement
and
$minElement
keys, the
boundaries are actual values. The lower bound is 500 and the upper bound is effec-
tively infinite. These values must share the same class of data type that you're querying
on; since you're querying on a number, the index bounds are numeric. If you were to
query on a string range instead, then the boundaries would be strings.
13
Before continuing on, try running
explain()
on a few queries of your own, and
pay attention to the difference between
n
and
nscanned
.
M
ONGO
DB'
S
QUERY
OPTIMIZER
AND
HINT
()
The query optimizer is the piece of software that determines which index, if any, will
most efficiently serve a given query. To select an ideal index for your queries, the
query optimizer uses a fairly simple set of rules:
Avoid
scanAndOrder
. If the query includes a sort, attempt to sort using an
index.
1
13
If this isn't making any sense, recall that a given index can contain keys of multiple data types. Thus, query
results will always be limited by the data type used in the query.
