Index Management

Create Index

db.collection.createIndex(
  {"field": 1},
  {
    background: true,
    name: "custom_index_name",
    unique: false
  }
)

Get Indexes

db.collection.getIndexes()

View Index Size

db.collection.totalIndexSize()

Delete Index

db.collection.dropIndex("INDEX-NAME")

explain Analysis

Three Modes

  1. queryPlanner mode (default): Returns the winning plan selected by query optimizer
  2. executionStats mode: Provides actual execution statistics
  3. allPlansExecution mode: Most detailed execution plan analysis

executionStats Core Metrics

executionTimeMillis

  • Complete execution time for the entire query statement

Document and Index Scan Counts

  • nReturned: Actual number of documents returned by query
  • totalKeysExamined: Number of index entries scanned
  • totalDocsExamined: Number of complete documents scanned

Ideal state: nReturned = totalKeysExamined = totalDocsExamined

Stage Status Analysis

Stages you want to see:

  • IDHACK: Direct query using _id field
  • IXSCAN: Index scan
  • FETCH: Retrieves complete documents based on index pointers

Stages you don’t want to see:

  • COLLSCAN: Full table scan
  • SORT: In-memory sort without using index

Performance Optimization Suggestions

  1. Create appropriate indexes for query conditions
  2. Compound indexes must follow leftmost prefix principle
  3. Use covered queries to avoid fetching documents
  4. Ensure sort fields have indexes established