Creation, viewing, and deletion of indexes

Index Creation

In MongoDB, indexes are key to improving query performance. The basic syntax for creating an index is db.collection.createIndex(). For example, creating an ascending index on the username field in a users collection:

db.users.createIndex({ username: 1 })

Compound indexes can include multiple fields, and queries will match according to the order of the indexed fields:

db.orders.createIndex({ customerId: 1, orderDate: -1 })

MongoDB supports various index types:

• Unique indexes: Ensure field values are unique
• Text indexes: Support text search
• Geospatial indexes: Support location-based queries
• Hashed indexes: Used for shard keys

Example of creating a unique index:

db.products.createIndex({ sku: 1 }, { unique: true })

TTL indexes can automatically delete expired documents:

db.logs.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

Viewing Indexes

To view all indexes in a collection, use the getIndexes() method:

db.orders.getIndexes()

The output will display index names, keys, types, and other information:

[
  {
    "v": 2,
    "key": { "_id": 1 },
    "name": "_id_"
  },
  {
    "v": 2,
    "key": { "customerId": 1, "orderDate": -1 },
    "name": "customerId_1_orderDate_-1"
  }
]

Use totalIndexSize() to check the space occupied by indexes:

db.orders.totalIndexSize()

The $indexStats operator retrieves index usage statistics:

db.orders.aggregate([ { $indexStats: { } } ])

Deleting Indexes

To delete a single index, use the dropIndex() method:

db.products.dropIndex("sku_1")

To delete all indexes (except the _id index), use dropIndexes():

db.customers.dropIndexes()

Rebuilding indexes can optimize index performance:

db.orders.reIndex()

In a sharded cluster, deleting an index requires connecting to mongos:

// Connect to mongos
shardConn = new Mongo("mongos.example.com:27017")
shardConn.getDB("mydb").orders.dropIndex("customerId_1")

Index Management Practices

Monitoring index usage is important. You can analyze queries using explain():

db.orders.find({ customerId: 123 }).explain("executionStats")

Regularly check for unused indexes:

db.orders.aggregate([
  { $indexStats: {} },
  { $match: { "accesses.ops": { $lt: 100 } } }
])

Partial indexes only include documents that meet specific conditions:

db.users.createIndex(
  { active: 1 },
  { partialFilterExpression: { active: true } }
)

Hidden indexes allow testing performance impact without deletion:

db.orders.hideIndex("customerId_1_orderDate_-1")
db.orders.unhideIndex("customerId_1_orderDate_-1")

Index Performance Optimization

Covered queries can return results directly from the index:

db.users.createIndex({ name: 1, email: 1 })
db.users.find({ name: "Zhang San" }, { _id: 0, email: 1 })

Index intersection allows MongoDB to combine multiple indexes:

db.products.createIndex({ category: 1 })
db.products.createIndex({ price: 1 })
db.products.find({ category: "Electronics", price: { $gt: 1000 } })

Index sort direction affects compound index efficiency:

// Suitable for scenarios where sort direction matches query direction
db.logs.createIndex({ timestamp: -1 })
db.logs.find().sort({ timestamp: -1 })

Index prefixes can utilize partial fields of a compound index:

db.orders.createIndex({ status: 1, orderDate: 1 })
// Can use the index on the status field
db.orders.find({ status: "shipped" })