- Indexes >
- Indexing Tutorials >
- Indexing Strategies >
- Use Indexes to Sort Query Results
Use Indexes to Sort Query Results¶
On this page
In MongoDB, sort operations can obtain the sort order by retrieving documents based on the ordering in an index. If the query planner cannot obtain the sort order from an index, it will sort the results in memory. Sort operations that use an index often have better performance than those that do not use an index. In addition, sort operations that do not use an index will abort when they use 32 megabytes of memory.
Sort with a Single Field Index¶
If an ascending or a descending index is on a single field, the sort operation on the field can be in either direction.
For example, create an ascending index on the field a
for a
collection records
:
This index can support an ascending sort on a
:
The index can also support the following descending sort on a
by
traversing the index in reverse order:
Sort on Multiple Fields¶
Create a compound index to support sorting on multiple fields.
You can specify a sort on all the keys of the index or on a subset;
however, the sort keys must be listed in the same order as they
appear in the index. For example, an index key pattern { a: 1, b: 1
}
can support a sort on { a: 1, b: 1 }
but not on { b: 1, a:
1 }
.
The sort must specify the same sort direction
(i.e.ascending/descending) for all its keys as the index key pattern or
specify the reverse sort direction for all its keys as the index key
pattern. For example, an index key pattern { a: 1, b: 1 }
can
support a sort on { a: 1, b: 1 }
and { a: -1, b: -1 }
but not
on { a: -1, b: 1 }
.
Sort and Index Prefix¶
If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern.
For example, create a compound index on the data
collection:
Then, the following are prefixes for that index:
The following query and sort operations use the index prefixes to sort the results. These operations do not need to sort the result set in memory.
Example | Index Prefix |
---|---|
db.data.find().sort( { a: 1 } ) |
{ a: 1 } |
db.data.find().sort( { a: -1 } ) |
{ a: 1 } |
db.data.find().sort( { a: 1, b: 1 } ) |
{ a: 1, b: 1 } |
db.data.find().sort( { a: -1, b: -1 } ) |
{ a: 1, b: 1 } |
db.data.find().sort( { a: 1, b: 1, c: 1 } ) |
{ a: 1, b: 1, c: 1 } |
db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } ) |
{ a: 1, b: 1 } |
Consider the following example in which the prefix keys of the index appear in both the query predicate and the sort:
In such cases, MongoDB can use the index to retrieve the documents in order specified by the sort. As the example shows, the index prefix in the query predicate can be different from the prefix in the sort.
Sort and Non-prefix Subset of an Index¶
An index can support sort operations on a non-prefix subset of the index key pattern. To do so, the query must include equality conditions on all the prefix keys that precede the sort keys.
For example, the collection data
has the following index:
The following operations can use the index to get the sort order:
Example | Index Prefix |
---|---|
db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } ) |
{ a: 1 , b: 1, c: 1 } |
db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } ) |
{ a: 1, b: 1, c: 1 } |
db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } ) |
{ a: 1, b: 1 } |
As the last operation shows, only the index fields preceding the sort subset must have the equality conditions in the query document; the other index fields may specify other conditions.
If the query does not specify an equality condition on an index
prefix that precedes or overlaps with the sort specification, the
operation will not efficiently use the index. For example, the
following operations specify a sort document of { c: 1 }
, but the
query documents do not contain equality matches on the preceding index
fields a
and b
:
These operations will not efficiently use the index { a: 1, b: 1,
c: 1, d: 1 }
and may not even use the index to retrieve the documents.