createIndexes

Command Fields

The createIndexes command takes the following fields:

Each document in the indexes array can take the following fields:

storageEngine: { <storage-engine-name>: <options> }

collation: {
   locale: <string>,
   caseLevel: <boolean>,
   caseFirst: <string>,
   strength: <int>,
   numericOrdering: <boolean>,
   alternate: <string>,
   maxVariable: <string>,
   backwards: <boolean>
}

wildcardProjection: {
  "path.to.field.a" : <value>,
  "path.to.field.b" : <value>
}

{
  "wildcardProjection" : {
    "_id" : 1,
    "<field>" : 0|1
  }
}

mongosh provides the methods db.collection.createIndex() and db.collection.createIndexes() as wrappers for the createIndexes command.

Index Names

The createIndexes command and mongosh helpers db.collection.createIndex() and db.collection.createIndexes() report an error if you create an index with one name, and then try to create the same index again but with another name.

{
   "ok" : 0,
   "errmsg" : "Index with name: x_1 already exists with a different name",
   "code" : 85,
   "codeName" : "IndexOptionsConflict"
}

In previous versions, MongoDB did not create the index again, but would return a response object with ok value of 1 and a note that implied that the index was not recreated. For example:

{
   "numIndexesBefore" : 2,
   "numIndexesAfter" : 2,
   "note" : "all indexes already exist",
   "ok" : 1
}

Replica Sets and Sharded Clusters

Starting with MongoDB 4.4, index builds on a replica set or sharded cluster build simultaneously across all data-bearing replica set members. For sharded clusters, the index build occurs only on shards containing data for the collection being indexed. The primary requires a minimum number of data-bearing voting members (i.e commit quorum), including itself, that must complete the build before marking the index as ready for use. See Index Builds in Replicated Environments for more information.

To start an index build with a non-default commit quorum, specify the commitQuorum.

MongoDB 4.4 adds the setIndexCommitQuorum command for modifying the commit quorum of an in-progress index build.

To minimize the impact of building an index on replica sets and sharded clusters, use a rolling index build procedure as described on Rolling Index Builds on Replica Sets.

Collation and Index Types

The following indexes only support simple binary comparison and do not support collation:

• Text indexes

• 2d indexes

Stable API

When using Stable API V1:

• You cannot specify any of the following fields in the indexes array:

  • background

  • bucketSize

  • sparse

  • storageEngine

• You cannot create geoHaystack or text indexes.

Concurrency

For featureCompatibilityVersion "4.2", createIndexes uses an optimized build process that obtains and holds an exclusive lock on the specified collection at the start and end of the index build. All subsequent operations on the collection must wait until createIndexes releases the exclusive lock. createIndexes allows interleaving read and write operations during the majority of the index build.

For featureCompatibilityVersion "4.0", createIndexes uses the pre-4.2 index build process which by default obtains an exclusive lock on the parent database for the entire duration of the build process. The pre-4.2 build process blocks all operations on the database and all its collections until the operation completed. background indexes do not take an exclusive lock.

For more information on the locking behavior of createIndexes, see Index Builds on Populated Collections.

Memory Usage Limit

createIndexes supports building one or more indexes on a collection. createIndexes uses a combination of memory and temporary files on disk to complete index builds. The default limit on memory usage for createIndexes is 200 megabytes (for versions 4.2.3 and later) and 500 (for versions 4.2.2 and earlier), shared between all indexes built using a single createIndexes command. Once the memory limit is reached, createIndexes uses temporary disk files in a subdirectory named _tmp within the --dbpath directory to complete the build.

You can override the memory limit by setting the maxIndexBuildMemoryUsageMegabytes server parameter. Setting a higher memory limit may result in faster completion of index builds. However, setting this limit too high relative to the unused RAM on your system can result in memory exhaustion and server shutdown.

Index Options

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

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

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

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

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

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

Wildcard Indexes

• Wildcard indexes omit the _id field by default. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document:

  {
    "wildcardProjection" : {
      "_id" : 1,
      "<field>" : 0|1
    }
  }

  All of the statements in the wildcardProjection document must be either inclusion or exclusion statements. You can also include the _id field with exclusion statements. This is the only exception to the rule.

• Wildcard indexes do not support:

  • 2d (Geospatial) indexes

  • 2dsphere (Geospatial) indexes

  • Hashed indexes

  • Time to Live (TTL) indexes

  • Text indexes

  • Unique indexes

  Wildcard indexes are sparse indexes. They do not support queries when an indexed field does not exist. A wildcard index will index the document if the wildcard field has a null value.

  Starting in MongoDB 7.0, wildcard indexes support ascending (1) and descending (-1) sort order. Earlier versions only supported ascending order.

To learn more, see:

• About Wildcard Indexes

• Wildcard Index Examples

• Wildcard Index Restrictions

Transactions

You can create collections and indexes inside a distributed transaction if the transaction is not a cross-shard write transaction.

To use createIndexes in a transaction, the transaction must use read concern "local". If you specify a read concern level other than "local", the transaction fails.

Commit Quorum Contrasted with Write Concern

There are important differences between commit quorums and write concerns:

• Index builds use commit quorums.

• Write operations use write concerns.

Each data-bearing node in a cluster is a voting member.

The commit quorum specifies how many data-bearing voting members, or which voting members, including the primary, must be prepared to commit a simultaneous index build. before the primary will execute the commit.

The write concern is the level of acknowledgement that the write has propagated to the specified number of instances.

The commit quorum specifies how many nodes must be ready to finish the index build before the primary commits the index build. In contrast, when the primary has committed the index build, the write concern specifies how many nodes must finish the index build before the command returns.

Create a Wildcard Index

The following lists examples of wildcard index creation:

• Create a Wildcard Index on a Single Field Path

• Create a Wildcard Index on All Field Paths

• Create a Wildcard Index on Multiple Specific Field Paths

• Create a Wildcard Index that Excludes Multiple Specific Field Paths

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "product_attributes.$**" : 1 },
        name: "wildcardIndex"
      }
    ]
  }
)

db.products_catalog.find( { "product_attributes.superFlight" : true } )
db.products_catalog.find( { "product_attributes.maxSpeed" : { $gt : 20 } } )
db.products_catalog.find( { "product_attributes.elements" : { $eq: "water" } } )

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        name: "wildcardIndex"
      }
    ]
  }
)

db.products_catalog.find( { "product_price" : { $lt : 25 } } )
db.products_catalog.find( { "product_attributes.elements" : { $eq: "water" } } )

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        "wildcardProjection" : {
          "product_attributes.elements" : 1,
          "product_attributes.resistance" : 1
        },
        name: "wildcardIndex"
      }
    ]
  }
)

db.products_catalog.find( { "product_attributes.elements" : { $eq: "Water" } } )
db.products_catalog.find( { "product_attributes.resistance" : "Bludgeoning" } )

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        "wildcardProjection" : {
           "product_attributes.elements" : 0,
           "product_attributes.resistance" : 0
        },
        name: "wildcardIndex"
      }
    ]
  }
)

db.products_catalog.find( { "product_attributes.maxSpeed" : { $gt: 25 } } )
db.products_catalog.find( { "product_attributes.superStrength" : true } )

Create Index With Commit Quorum

Starting with MongoDB 4.4, index builds on a replica set or sharded cluster build simultaneously across all data-bearing replica set members. For sharded clusters, the index build occurs only on shards containing data for the collection being indexed. The primary requires a minimum number of data-bearing voting members (i.e commit quorum), including itself, that must complete the build before marking the index as ready for use. See Index Builds in Replicated Environments for more information.

To set the commit quorum, use createIndexes to specify the commitQuorum value.

commitQuorum specifies how many data-bearing voting members, or which voting members, including the primary, must be prepared to commit the index build before the primary will execute the commit. The default commit quorum is votingMembers, which means all data-bearing members.

The following operation creates an index with a commit quorum of "majority", or a simple majority of data-bearing members:

db.getSiblingDB("examples").runCommand(
  {
    createIndexes: "invoices",
    indexes: [
      {
        key: { "invoices" : 1 },
        "name" : "invoiceIndex"
      }
    ],
    "commitQuorum" : "majority"
  }
)

The primary marks index build as ready only after a simple majority of data-bearing voting members "vote" to commit the index build. For more information on index builds and the voting process, see Index Builds in Replicated Environments.