Create, Get & Drop Indexes

Without an index, MongoDB performs a collection scan (COLLSCAN) — reading every document. On millions of rows, that turns milliseconds into seconds.

Why indexes matter

Without an index, MongoDB performs a collection scan (COLLSCAN) — reading every document. On millions of rows, that turns milliseconds into seconds.

Indexes are B-tree structures keyed by field values. The query planner chooses IXSCAN when an index supports your filter and sort.

Real-life example: An index is the alphabetized index at the back of a textbook — you jump to page 214 instead of flipping every page.

Index vs collection scan

createIndex — define access paths

createIndex builds an index in the background on standalone instances (check build progress for large collections).

Direction 1 is ascending, −1 descending — important for sort compatibility on compound indexes.

Real-life example: Indexing email is like sorting the student roster by email — lookups by email become instant.

Common createIndex patterns

// Single field — login by email
db.users.createIndex({ email: 1 })

// Compound — user's orders newest first
db.orders.createIndex({ userId: 1, createdAt: -1 })

// Unique — prevent duplicate SKUs
db.products.createIndex({ sku: 1 }, { unique: true })

// Named index (easier to drop later)
db.students.createIndex(
  { grade: 1, "address.city": 1 },
  { name: "grade_city_idx" }
)

getIndexes and dropIndex

getIndexes lists index name, keys, and options — always check before dropping.

dropIndex takes the index name (not the field list). dropIndexes drops all but _id.

Real-life example: getIndexes is inventory of every shortcut file in the cabinet; dropIndex removes one outdated shortcut you no longer need.

Every index speeds reads but slows writes — do not index every field. Index what your slow queries actually filter and sort on.

Inspect and remove indexes

// List all indexes on a collection
db.products.getIndexes()

// Drop by name
db.products.dropIndex("sku_1")

// Drop all custom indexes (keeps _id_)
db.products.dropIndexes()

explain() — COLLSCAN vs IXSCAN

explain('executionStats') shows the winning plan, docs examined, and execution time — run this on every production query you care about.

IXSCAN means index scan (good at scale). COLLSCAN means full collection scan (fine for tiny collections, dangerous on large ones).

Real-life example: explain() is a shop security camera replay — you see whether the clerk checked the indexed ledger or searched every box in the warehouse.

Compare plans before and after indexing

// Before index — likely COLLSCAN
db.orders.find({ userId: ObjectId("..."), status: "pending" })
  .sort({ createdAt: -1 })
  .explain("executionStats")

// Create supporting index
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// After index — look for IXSCAN
db.orders.find({ userId: ObjectId("..."), status: "pending" })
  .sort({ createdAt: -1 })
  .explain("executionStats")

// Key fields in output:
// executionStats.executionStages.stage → "IXSCAN" or "COLLSCAN"
// executionStats.totalDocsExamined vs nReturned → should be close