Use Indexes to Sort Query Results

In MongoDB, sort operations can obtain the sort order by retrievingdocuments based on the ordering in an index. If the query plannercannot obtain the sort order from an index, it will sort the results inmemory. Sort operations that use an index often have better performancethan those that do not use an index. In addition, sort operations thatdo not use an index will abort when they use 32 megabytes of memory.

Note

As a result of changes to sorting behavior on array fields in MongoDB3.6, when sorting on an array indexed with amultikey index the query plan includesa blocking SORT stage. The new sorting behavior may negatively impactperformance.

In a blocking SORT, all input must be consumed by the sort step beforeit can produce output. In a non-blocking, or indexed sort, thesort step scans the index to produce results in the requested order.

Sort with a Single Field Index

If an ascending or a descending index is on a single field, the sortoperation on the field can be in either direction.

For example, create an ascending index on the field a for acollection records:

db.records.createIndex( { a: 1 } )

This index can support an ascending sort on a:

db.records.find().sort( { a: 1 } )

The index can also support the following descending sort on a bytraversing the index in reverse order:

db.records.find().sort( { a: -1 } )

Sort on Multiple Fields

Create a compound index to support sortingon 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 theyappear 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 }.

For a query to use a compound index for a sort, the specified sort directionfor all keys in the cursor.sort() document must match the indexkey pattern or match the inverse of the index key pattern.For example, an index key pattern { a: 1, b: -1 } cansupport a sort on { a: 1, b: -1 } and { a: -1, b: 1 } but noton { a: -1, b: -1 } or {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 acompound index is a subset that consists of one or more keys at thestart of the index key pattern.

For example, create a compound index on the data collection:

db.data.createIndex( { a:1, b: 1, c: 1, d: 1 } )

Then, the following are prefixes for that index:

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

The following query and sort operations use the index prefixes to sortthe results. These operations do not need to sort the result set inmemory.

Consider the following example in which the prefix keys of the indexappear in both the query predicate and the sort:

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

In such cases, MongoDB can use the index to retrieve the documents inorder specified by the sort. As the example shows, the index prefix inthe 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 theindex key pattern. To do so, the query must include equalityconditions on all the prefix keys that precede the sort keys.

For example, the collection data has the following index:

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

The following operations can use the index to get the sort order:

As the last operation shows, only the index fields preceding the sortsubset must have the equality conditions in the query document; theother index fields may specify other conditions.

If the query does not specify an equality condition on an indexprefix that precedes or overlaps with the sort specification, theoperation will not efficiently use the index. For example, thefollowing operations specify a sort document of { c: 1 }, but thequery documents do not contain equality matches on the preceding indexfields a and b:

db.data.find( { a: { $gt: 2 } } ).sort( { c: 1 } )
db.data.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.

Index Use and Collation

To use an index for string comparisons, an operation must alsospecify the same collation. That is, an index with a collationcannot support an operation that performs string comparisons on theindexed fields if the operation specifies a different collation.

For example, the collection myColl has an index on a stringfield category with the collation locale "fr".

db.myColl.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

The following query operation, which specifies the same collation asthe index, can use the index:

db.myColl.find( { category: "cafe" } ).collation( { locale: "fr" } )

However, the following query operation, which by default uses the“simple” binary collator, cannot use the index:

db.myColl.find( { category: "cafe" } )

For a compound index where the index prefix keys are not strings,arrays, and embedded documents, an operation that specifies adifferent collation can still use the index to support comparisonson the index prefix keys.

For example, the collection myColl has a compound index on thenumeric fields score and price and the string fieldcategory; the index is created with the collation locale"fr" for string comparisons:

db.myColl.createIndex(
   { score: 1, price: 1, category: 1 },
   { collation: { locale: "fr" } } )

The following operations, which use "simple" binary collationfor string comparisons, can use the index:

db.myColl.find( { score: 5 } ).sort( { price: 1 } )
db.myColl.find( { score: 5, price: { $gt: NumberDecimal( "10" ) } } ).sort( { price: 1 } )

The following operation, which uses "simple" binary collationfor string comparisons on the indexed category field, can usethe index to fulfill only the score: 5 portion of the query:

db.myColl.find( { score: 5, category: "cafe" } )