OPTIONS

Use Indexes to Sort Query Results

In MongoDB sort operations that sort documents based on an indexed field provide the greatest performance. Indexes in MongoDB, as in other databases, have an order: as a result, using an index to access documents returns in the same order as the index.

To sort on multiple fields, create a compound index. With compound indexes, the results can be in the sorted order of either the full index or an index prefix. An index prefix is a subset of a compound index; the subset consists of one or more fields at the start of the index, in order. For example, given an index { a:1, b: 1, c: 1, d: 1 }, the following subsets are index prefixes:

{ a: 1 }
{ a: 1, b: 1 }
{ a: 1, b: 1, c: 1 }

For more information on sorting by index prefixes, see Sort Subset Starts at the Index Beginning.

If the query includes equality match conditions on an index prefix, you can sort on a subset of the index that starts after or overlaps with the prefix. For example, given an index { a: 1, b: 1, c: 1, d: 1 }, if the query condition includes equality match conditions on a and b, you can specify a sort on the subsets { c: 1 } or { c: 1, d: 1 }:

db.collection.find( { a: 5, b: 3 } ).sort( { c: 1 } )
db.collection.find( { a: 5, b: 3 } ).sort( { c: 1, d: 1 } )

In these operations, the equality match and the sort documents together cover the index prefixes { a: 1, b: 1, c: 1 } and { a: 1, b: 1, c: 1, d: 1 } respectively.

You can also specify a sort order that includes the prefix; however, since the query condition specifies equality matches on these fields, they are constant in the resulting documents and do not contribute to the sort order:

db.collection.find( { a: 5, b: 3 } ).sort( { a: 1, b: 1, c: 1 } )
db.collection.find( { a: 5, b: 3 } ).sort( { a: 1, b: 1, c: 1, d: 1 } )

For more information on sorting by index subsets that are not prefixes, see Sort Subset Does Not Start at the Index Beginning.

Note

For in-memory sorts that do not use an index, the sort() operation is significantly slower. The sort() operation will abort when it uses 32 megabytes of memory.

Sort With a Subset of Compound Index

If the sort document contains a subset of the compound index fields, the subset can determine whether MongoDB can use the index efficiently to both retrieve and sort the query results. If MongoDB can efficiently use the index to both retrieve and sort the query results, the output from the explain() will display scanAndOrder as false or 0. If MongoDB can only use the index for retrieving documents that meet the query criteria, MongoDB must manually sort the resulting documents without the use of the index. For in-memory sort operations, explain() will display scanAndOrder as true or 1.

Sort Subset Starts at the Index Beginning

If the sort document is a subset of a compound index and starts from the beginning of the index, MongoDB can use the index to both retrieve and sort the query results.

For example, the collection collection has the following index:

{ a: 1, b: 1, c: 1, d: 1 }

The following operations include a sort with a subset of the index. Because the sort subset starts at beginning of the index, the operations can use the index for both the query retrieval and sort:

db.collection.find().sort( { a:1 } )
db.collection.find().sort( { a:1, b:1 } )
db.collection.find().sort( { a:1, b:1, c:1 } )

db.collection.find( { a: 4 } ).sort( { a: 1, b: 1 } )
db.collection.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } )

db.collection.find( { b: 5 } ).sort( { a: 1, b: 1 } )
db.collection.find( { b: { $gt:5 }, c: { $gt: 1 } } ).sort( { a: 1, b: 1 } )

The last two operations include query conditions on the field b but does not include a query condition on the field a:

db.collection.find( { b: 5 } ).sort( { a: 1, b: 1 } )
db.collection.find( { b: { $gt:5 }, c: { $gt: 1 } } ).sort( { a: 1, b: 1 } )

Consider the case where the collection has the index { b: 1 } in addition to the { a: 1, b: 1, c: 1, d: 1 } index. Because of the query condition on b, it is not immediately obvious which index MongoDB may select as the “best” index. To explicitly specify the index to use, see hint().

Sort Subset Does Not Start at the Index Beginning

The sort document can be a subset of a compound index that does not start from the beginning of the index. For instance, { c: 1 } is a subset of the index { a: 1, b: 1, c: 1, d: 1 } that omits the preceding index fields a and b. MongoDB can use the index efficiently if the query document includes all the preceding fields of the index, in this case a and b, in equality conditions. In other words, the equality conditions in the query document and the subset in the sort document contiguously cover a prefix of the index.

For example, the collection collection has the following index:

{ a: 1, b: 1, c: 1, d: 1 }

Then following operations can use the index efficiently:

db.collection.find( { a: 5 } ).sort( { b: 1, c: 1 } )
db.collection.find( { a: 5, c: 4, b: 3 } ).sort( { d: 1 } )
  • In the first operation, the query document { a: 5 } with the sort document { b: 1, c: 1 } cover the prefix { a:1 , b: 1, c: 1 } of the index.
  • In the second operation, the query document { a: 5, c: 4, b: 3 } with the sort document { d: 1 } covers the full index.

Only the index fields preceding the sort subset must have the equality conditions in the query document. The other index fields may have other conditions. The following operations can efficiently use the index since the equality conditions in the query document and the subset in the sort document contiguously cover a prefix of the index:

db.collection.find( { a: 5, b: 3 } ).sort( { c: 1 } )
db.collection.find( { a: 5, b: 3, c: { $lt: 4 } } ).sort( { c: 1 } )

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:

db.collection.find( { a: { $gt: 2 } } ).sort( { c: 1 } )
db.collection.find( { c: 5 } ).sort( { c: 1 } )

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.