/
index-compound.txt
153 lines (109 loc) · 4.86 KB
/
index-compound.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
.. index:: index; compound
.. index:: compound index
.. _index-type-compound:
================
Compound Indexes
================
.. default-domain:: mongodb
.. contents:: On this page
:local:
:backlinks: none
:depth: 1
:class: singlecol
MongoDB supports *compound indexes*, where a single index structure
holds references to multiple fields [#compound-index-field-limit]_
within a collection's documents. The following diagram illustrates an
example of a compound index on two fields:
.. include:: /images/index-compound-key.rst
.. [#compound-index-field-limit]
MongoDB imposes a :limit:`limit of 31 fields for any compound index
<Number of Indexed Fields in a Compound Index>`.
Compound indexes can support queries that match on multiple fields.
.. example::
Consider a collection named
``products`` that holds documents that resemble the following
document:
.. code-block:: javascript
{
"_id": ObjectId(...)
"item": "Banana"
"category": ["food", "produce", "grocery"]
"location": "4th Street Store"
"stock": 4
"type": cases
"arrival": Date(...)
}
If applications query on the ``item`` field as well as query on both
the ``item`` field and the ``stock`` field, you can specify a single
compound index to support both of these queries:
.. code-block:: javascript
db.products.ensureIndex( { "item": 1, "stock": 1 } )
.. important:: You may not create compound indexes that have
``hashed`` index fields. You will receive an error if you attempt to
create a compound index that includes :doc:`a hashed index
</core/index-hashed>`.
The order of the fields in a compound index is very important. In the
previous example, the index will contain references to documents sorted
first by the values of the ``item`` field and, within each value of the
``item`` field, sorted by values of the ``stock`` field. See
:ref:`index-ascending-and-descending` for more information.
In addition to supporting queries that match on all the index fields,
compound indexes can support queries that match on the prefix of the
index fields. For details, see :ref:`compound-index-prefix`.
.. index:: index; sort order
.. _index-ascending-and-descending:
Sort Order
----------
Indexes store references to fields in either ascending (``1``) or
descending (``-1``) sort order. For single-field indexes, the sort
order of keys doesn't matter because MongoDB can traverse the index in
either direction. However, for :ref:`compound indexes
<index-type-compound>`, sort order can matter in determining whether
the index can support a sort operation.
Consider a collection ``events`` that contains documents with the
fields ``username`` and ``date``. Applications can issue queries that
return results sorted first by ascending ``username`` values and then
by descending (i.e. more recent to last) ``date`` values, such as:
.. code-block:: javascript
db.events.find().sort( { username: 1, date: -1 } )
or queries that return results sorted first by descending ``username``
values and then by ascending ``date`` values, such as:
.. code-block:: javascript
db.events.find().sort( { username: -1, date: 1 } )
The following index can support both these sort operations:
.. code-block:: javascript
db.events.ensureIndex( { "username" : 1, "date" : -1 } )
However, the above index cannot support sorting by ascending
``username`` values and then by ascending ``date`` values, such as the
following:
.. code-block:: javascript
db.events.find().sort( { username: 1, date: 1 } )
.. _compound-index-prefix:
Prefixes
--------
Compound indexes support queries on any prefix of the index
fields. Index prefixes are the beginning subset of indexed fields. For
example, given the index ``{ a: 1, b: 1, c: 1 }``, both ``{ a: 1 }``
and ``{ a: 1, b: 1 }`` are prefixes of the index.
If you have a collection that has a compound index on ``{ a: 1, b:
1 }``, as well as an index that consists of the prefix of that index,
i.e. ``{ a: 1 }``, assuming none of the index has a sparse or unique
constraints, then you can drop the ``{ a: 1 }`` index. MongoDB will be
able to use the compound index in all of situations that it would have
used the ``{ a: 1 }`` index.
.. example::
Given the following index:
.. code-block:: javascript
{ "item": 1, "location": 1, "stock": 1 }
MongoDB **can** use this index to support queries that include:
- the ``item`` field,
- the ``item`` field *and* the ``location`` field,
- the ``item`` field *and* the ``location`` field *and* the
``stock`` field, or
- only the ``item`` *and* ``stock`` fields; however, this index
would be less efficient than an index on only ``item`` and
``stock``.
MongoDB **cannot** use this index to support queries that include:
- only the ``location`` field,
- only the ``stock`` field, or
- only the ``location`` *and* ``stock`` fields.