Database Reference
In-Depth Information
>>>
>>>
db
.
events
.
ensure_index
([(
'host'
,
1
), (
'time'
,
1
)])
Now,
explain()
tells us the following:
{
...
u'cursor'
:
u'BtreeCursor host_1_time_1'
,
u'indexBounds'
: {
u'host'
: [[
u'127.0.0.1'
,
u'127.0.0.1'
]],
u'time'
: [[
datetime
.
datetime
(
2000
,
10
,
10
,
0
,
0
),
datetime
.
datetime
(
2000
,
10
,
11
,
0
,
0
)]]},
...
u'millis'
:
0
,
u'n'
:
11
,
...
u'nscanned'
:
11
,
u'nscannedObjects'
:
11
,
...
}
Here, the query had to scan 11 items from the index before returning 11 objects in less than
a millisecond. Although the index order has an impact on query performance, remember that
index scans are
much
faster than collection scans, and depending on your other queries, it may
make more sense to use the
{ time: 1, host: 1 }
index depending on usage profile.
Rules of index design
MongoDB indexes are stored in a data structure known as a B-tree. The details are beyond our
scope here, but what you need to understand as a MongoDB
user
is that each index is stored
in sorted order on all the fields in the index. For an index to be maximally efficient, the key
should look just like the queries that use the index. Ideally, MongoDB should be able to tra-
verse the index to the first document that the query returns and
sequentially walk
the index to
find the rest.
Because of this sorted B-tree structure, then, the following rules will lead to efficient indexes:
▪ Any fields that will be queried
by equality
should occur first in the index definition.
▪ Fields used to sort should occur next in the index definition. If multiple fields are being
sorted (such as
(last_name, first_name)
, then they should occur in the same order in
the index definition.
▪ Fields that are queried by range should occur
last
in the index definition.
