Skip to main content

MongoDB Indexing

Basic Index Operations​

Create Indexes​

// Single field index
db.users.createIndex({ email: 1 });

// Compound index
db.users.createIndex({ status: 1, createdAt: -1 });

// Unique index
db.users.createIndex({ email: 1 }, { unique: true });

// Sparse index (only for documents with the field)
db.users.createIndex({ phone: 1 }, { sparse: true });

// Partial index (with condition)
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true } }
);

// TTL index (expire documents)
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 3600 }
);

Index Types​

// Text index
db.articles.createIndex({ title: "text", content: "text" });

// 2dsphere index (geospatial)
db.places.createIndex({ location: "2dsphere" });

// Hashed index (for sharding)
db.users.createIndex({ userId: "hashed" });

// Wildcard index
db.products.createIndex({ "specs.$**": 1 });

Index Management​

// List all indexes
db.users.getIndexes();

// Drop index
db.users.dropIndex({ email: 1 });
db.users.dropIndex("email_1");

// Drop all indexes (except _id)
db.users.dropIndexes();

// Rebuild indexes
db.users.reIndex();

// Get index stats
db.users.aggregate([{ $indexStats: {} }]);

Index Best Practices​

ESR Rule for Compound Indexes​

// Equality, Sort, Range
db.orders.createIndex({
  customerId: 1,    // Equality filter
  createdAt: -1,    // Sort field
  amount: 1         // Range filter
});

// Query that uses this index efficiently
db.orders.find({
  customerId: "123",           // Equality
  amount: { $gte: 100 }        // Range
}).sort({ createdAt: -1 });    // Sort

Index Intersection​

// Instead of one large compound index
db.products.createIndex({ category: 1, brand: 1, price: 1, rating: 1 });

// Create smaller indexes that can intersect
db.products.createIndex({ category: 1 });
db.products.createIndex({ brand: 1 });
db.products.createIndex({ price: 1 });

Covering Queries​

// Index that covers the entire query
db.users.createIndex({ status: 1, name: 1, email: 1 });

// Query that only needs index data
db.users.find(
  { status: "active" },
  { name: 1, email: 1, _id: 0 }
);

Index Analysis​

Explain Query Plans​

// Basic explain
db.users.find({ email: "test@example.com" }).explain();

// Execution stats
db.users.find({ email: "test@example.com" }).explain("executionStats");

// All plans
db.users.find({ age: { $gt: 25 } }).explain("allPlansExecution");

Index Usage Analysis​

// Find unused indexes
db.users.aggregate([{ $indexStats: {} }]).forEach(
  function(index) {
    if (index.accesses.ops === 0) {
      print("Unused index: " + index.name);
    }
  }
);

// Analyze slow operations
db.setProfilingLevel(1, { slowms: 100 });
db.system.profile.find().limit(5).sort({ ts: -1 }).pretty();

Index Optimization Examples​

Optimize Date Range Queries​

// For date range queries
db.events.createIndex({ eventDate: 1, type: 1 });

// Query pattern
db.events.find({
  eventDate: {
    $gte: ISODate("2024-01-01"),
    $lt: ISODate("2024-02-01")
  },
  type: "click"
});

Optimize Text Search​

// Text index with weights
db.articles.createIndex(
  {
    title: "text",
    content: "text",
    tags: "text"
  },
  {
    weights: {
      title: 10,
      content: 5,
      tags: 1
    },
    name: "article_text_index"
  }
);

Optimize Array Queries​

// Multikey index for arrays
db.products.createIndex({ tags: 1 });

// Query array elements
db.products.find({ tags: "electronics" });
db.products.find({ tags: { $in: ["electronics", "gadgets"] } });

Optimize Nested Field Queries​

// Index on nested fields
db.users.createIndex({ "profile.location.city": 1 });
db.orders.createIndex({ "shipping.address.zipCode": 1 });

// Wildcard index for flexible nested queries
db.products.createIndex({ "specifications.$**": 1 });

Performance Monitoring​

Index Hit Ratio​

// Check index usage in current operations
db.currentOp({"command.find": {$exists: true}});

// Server status for index metrics
db.serverStatus().indexCounters;

Memory Usage​

// Index sizes
db.stats().indexSizes;

// Collection stats including index info
db.users.stats();

Common Index Patterns​

User Authentication​

// Unique email for login
db.users.createIndex({ email: 1 }, { unique: true });

// Session management
db.sessions.createIndex(
  { sessionId: 1 },
  { unique: true, partialFilterExpression: { active: true } }
);

E-commerce Product Catalog​

// Product search and filtering
db.products.createIndex({ category: 1, brand: 1, price: 1 });
db.products.createIndex({ name: "text", description: "text" });
db.products.createIndex({ "ratings.average": -1 });

Time-series Data​

// Efficient time-based queries
db.metrics.createIndex({ deviceId: 1, timestamp: -1 });

// TTL for data retention
db.logs.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 2592000 } // 30 days
);

Geospatial Applications​

// Location-based queries
db.stores.createIndex({ location: "2dsphere" });

// Find nearby stores
db.stores.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [-73.97, 40.77] },
      $maxDistance: 1000
    }
  }
});