> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

# Use Indexes to Sort Query Results

If the collection has an index that includes the sort fields, MongoDB can use that index to obtain the results of a sort operation. In [`$or`](https://www.mongodb.com/docs/manual/reference/operator/query/or.md#mongodb-query-op.-or) queries, MongoDB may use multiple indexes to support a single sort operation, because each clause of the `$or` expression can use its own index.

If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform an in-memory sort operation on the data.

Starting in MongoDB 6.0, if the server requires more than 100 megabytes of memory for a pipeline execution stage, MongoDB automatically writes temporary files to disk unless that query specifies `{ allowDiskUse: false }`.

Sort operations that use an index often have better performance than in-memory sorts.

**Note:**

When you sort based on an array field that is indexed with a [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey), the query plan includes an [in-memory sort](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-in-memory-sort) stage unless both of the following are true:

- The index [boundaries](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-multikey-index-bounds-intersecting) for all sort fields are `[MinKey, MaxKey]`.

- No boundaries for any multikey-indexed field have the same path prefix as the sort pattern.

## 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`:

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

This index can support an ascending sort on `a`:

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

The index can also support the following descending sort on `a` by traversing the index in reverse order:

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

## Sort on Multiple Fields

Create a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) 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 }`.

For a query to use a compound index for a sort, the specified sort direction for all keys in the [`cursor.sort()`](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#mongodb-method-cursor.sort) document must match the index key pattern *or* match the inverse of 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 }` 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 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:

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

Then, the following are prefixes for that index:

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

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:

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

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:

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

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`:

```javascript
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 Sort Order

A collection of indexed documents may have multiple data types in the key field. When an index has a key with multiple data types, the index is sorted according to the [BSON type sort order.](https://www.mongodb.com/docs/manual/reference/bson-type-comparison-order.md#std-label-bson-types-comparison-order)

See the [index sorting example.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#std-label-ex-sort-index-types)

## Index Use and Collation

To use an index for string comparisons, an operation must also specify the same collation. If an operation specifies a different collation than the index specifies, the index cannot support string comparisons on the indexed fields.

**Warning:**

Collation-aware index keys might be larger than index keys for indexes without collation because indexes that are configured with collation use ICU collation keys to achieve sort order.

## Examples

The following example demonstrates sorting when index keys have the same or different types.

Create the `keyTypes` collection:

```javascript
db.keyTypes.insertMany( [
  { seqNum: 1, seqType: null, type: "null" },
  { seqNum: 29, seqType: null, type: "null" },
  { seqNum: 2, seqType: Int32("10"), type: "Int32"  },
  { seqNum: 28, seqType: Int32("10"), type: "Int32"  },
  { seqNum: 3, seqType: Long("10"), type: "Long" },
  { seqNum: 27, seqType: Long("10"), type: "Long" },
  { seqNum: 4, seqType: Decimal128("10"), type: "Decimal128" },
  { seqNum: 26, seqType: Decimal128("10"), type: "Decimal128" },
  { seqNum: 5, seqType: Double("10"), type: "Double" },
  { seqNum: 25, seqType: Double("10"), type: "Double"  },
  { seqNum: 6, seqType: String("10"), type: "String"  },
  { seqNum: 24, seqType: String("10"), type: "String" },
  { seqNum: 7, seqType: [ "1", "2", "3" ], type: "Array" },
  { seqNum: 23, seqType: [ "1", "2", "3" ], type: "Array" },
  { seqNum: 8, seqType: [ [1], [2], [3] ], type: "Array" },
  { seqNum: 22, seqType: [ [1], [2], [3] ], type: "Array " },
  { seqNum: 9, seqType: [ 1, 2, 3 ], type: "Array" },
  { seqNum: 21, seqType: [ 1, 2, 3 ], type: "Array" },
  { seqNum: 10, seqType: true, type: "Boolean" },
  { seqNum: 11, seqType: new Timestamp(), type: "Timestamp" },
  { seqNum: 12, seqType: new Date(), type: "Date" },
  { seqNum: 13, seqType: new ObjectId(), type: "ObjectId" },
] )
```

Create indexes on the sequence number ( `seqNum` ) and sequence type ( `seqType` ) fields:

```javascript
db.keyTypes.createIndex( { seqNum: 1 } )

db.keyTypes.createIndex( { seqType: 1 } )
```

Query the collection using [`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#mongodb-method-db.collection.find). The projection document, `{ _id: 0 }`, suppresses the `_id` field in the output display.

```javascript
db.keyTypes.find( {}, { _id: 0 } )
```

The documents are returned in insertion order:

```javascript:
{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 29, seqType: null, type: 'null' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 28, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 27, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },

// Output truncated
```

The sequence number ( `seqNum` ) index has values of the same type. Use the `seqNum` index to query the `keyTypes` collection:

```javascript
db.keyTypes.find( {}, { _id: 0 } ).sort( { seqNum: 1} )
```

The `seqNum` keys are integers. The documents are returned in numerical order:

```javascript:
{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 5, seqType: 10, type: 'Double' },
{ seqNum: 6, seqType: '10', type: 'String' },
{ seqNum: 7, seqType: [ '1', '2', '3' ], type: 'Array' },

// Output truncated
```

The sequence type ( `seqType` ) index has values of the different types. Use the `seqType` index to query the `keyTypes` collection:

```javascript
db.keyTypes.find( {}, { _id: 0 } ).sort( { seqType: 1} )
```

The documents are returned in [BSON type sort order:](https://www.mongodb.com/docs/manual/reference/bson-type-comparison-order.md#std-label-bson-types-comparison-order)

```javascript:
{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 29, seqType: null, type: 'null' },
{ seqNum: 9, seqType: [ 1, 2, 3 ], type: 'Array' },
{ seqNum: 21, seqType: [ 1, 2, 3 ], type: 'Array' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 28, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 27, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 26, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 5, seqType: 10, type: 'Double' },
{ seqNum: 25, seqType: 10, type: 'Double' },
{ seqNum: 7, seqType: [ '1', '2', '3' ], type: 'Array' },
{ seqNum: 23, seqType: [ '1', '2', '3' ], type: 'Array' },
{ seqNum: 6, seqType: '10', type: 'String' },
{ seqNum: 24, seqType: '10', type: 'String' },
{ seqNum: 8, seqType: [ [ 1 ], [ 2 ], [ 3 ] ], type: 'Array' },
{ seqNum: 22, seqType: [ [ 1 ], [ 2 ], [ 3 ] ], type: 'Array ' },
{
  seqNum: 13,
  seqType: ObjectId("6239e3922604d5a7478df071"),
  type: 'ObjectId'
},
{ seqNum: 10, seqType: true, type: 'Boolean' },
{
  seqNum: 12,
  seqType: ISODate("2022-03-22T14:56:18.100Z"),
  type: 'Date'
},
{
  seqNum: 11,
  seqType: Timestamp({ t: 1647960978, i: 1 }),
  type: 'Timestamp'
}
```

- Numerical types (Int32, Long, Decimal128, Double) are equivalent when compared with other types.

- Within the Numbers BSON type, numerical types are sorted:

  - Int32

  - Long

  - Decimal128

  - Double