Database Reference
In-Depth Information
Range queries
A range query with or without a sort on the same field. For example, all closing values
greater than or equal to 100:
db.values.find({close: {$gte: 100})
If you add a sort clause on the same key, the optimizer will still be able to use the same
index:
db.values.find({close: {$gte: 100}).sort({close: 1})
C
OMPOUND
-
KEY
INDEXES
Compound-key indexes are a little more complicated, but their uses are analogous to
those of single-key indexes. The main thing to remember is that a compound-key
index can efficiently serve just a single range or sort per query. Let's imagine a triple-
compound key index, again for stock values, on
{close:
1,
open:
1,
date:
1}
. Fol-
lowing are some possible scenarios.
Exact matches
An exact match on the first key, the first and second keys, or the first, second, and
third keys, in that order:
db.values.find({close: 1})
db.values.find({close: 1, open: 1})
db.values.find({close: 1, open: 1, date: "1985-01-08"})
Range matches
An exact match on any set of leftmost keys (including none), followed by either a
range or a sort using the next key to the right. Thus, all the following queries are ideal
for the triple-key index:
db.values.find({}).sort({close: 1})
db.values.find({close: {$gt: 1}})
db.values.find({close: 100}).sort({open: 1})
db.values.find({close: 100, open: {$gt: 1}})
db.values.find({close: 1, open: 1.01, date: {$gt: "2005-01-01"}})
db.values.find({close: 1, open: 1.01}).sort({date: 1})
C
OVERING
INDEXES
If you've never heard of
covering indexes
, then realize from the start that the term is
something of a misnomer. A covering index isn't, as the name would suggest, a
kind
of
index but rather a
special use
of an index. In particular, an index can be said to cover a
query if all the data required by the query resides in the index itself. Covered index
queries are also known as
index-only queries
, since these queries are served without hav-
ing to reference the indexed documents themselves. This can result in increased
query performance.
Using a covering index in MongoDB is easy. Simply select a set of fields that reside
in a single index, and exclude the
_id
field (since this field likely isn't part of the