Database Reference
In-Depth Information
Look at the value for
nscanned
: 5,299. This is much greater than the 894 entries
scanned previously, and the time it takes to complete the query bears this out.
All that's left to understand is how the query optimizer caches and expires its
choice of query plan. After all, you wouldn't want the optimizer running all those
plans in parallel on each query.
When a successful plan is discovered, the query pattern, the value for
nscanned
,
and the index spec are recorded. For the query we've been working with, the
recorded structure looks something like this:
{ pattern: {stock_symbol: 'equality', close: 'bound',
index: {stock_symbol: 1},
nscanned: 894 }
The query pattern records the kind of match for each key. Here, you're requesting an
exact match on
stock_symbol
(equality), and a range match on
close
(bound).
15
Whenever a new query matches this pattern, the index will be used.
But this shouldn't hold forever, and it doesn't. The optimizer automatically expires
a plan after any of the following events:
100 writes are made to the collection.
Indexes are added or removed from the collection.
A query using a cached query plan does a lot more work than expected. Here,
what qualifies as “a lot more work” is a value for
nscanned
exceeding the cached
nscanned
value by at least a factor of 10.
In the last of these cases, the optimizer will immediately begin interleaving other
query plans in case a different index proves more efficient.
7.3.3
Query patterns
Here I present several common query patterns and the indexes to use with them.
S
INGLE
-
KEY
INDEXES
To r e v i e w s in g l e - k e y i n d e x e s , r e ca l l t h e in d e x y o u c r e a t e d f o r t h e s t o ck v a l u e s c o l l e c t i o n
on closing numbers,
{close:
1}
. This index can be used in the following scenarios.
Exact matches
An exact match. For instance, all entries with a closing value of 100:
db.values.find({close: 100})
Sorting
A sort on the indexed field. For example:
db.values.find({}).sort({close: 1})
In the case of a sort with no query selector, you'll probably want to tack on a limit
unless you actually plan to iterate over the entire collection.
15
In case you're interested, three kinds of range matches are stored: upper, lower, and upper-and-lower. The
query pattern also includes any sort specification.
