Database Tutorial #12: MongoDB Indexing and Performance

// Create an index on the email field
db.users.createIndex({ email: 1 })  // 1 = ascending, -1 = descending

// The query now uses the index
db.users.find({ email: "alex@example.com" })

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

// Index for filtering by user_id and sorting by createdAt
db.orders.createIndex({ user_id: 1, createdAt: -1 })

// Uses the index (matches leading fields)
db.orders.find({ user_id: "abc" }).sort({ createdAt: -1 })

// Also uses the index (partial match on leading field)
db.orders.find({ user_id: "abc" })

// Does NOT use the index (skips the leading field)
db.orders.find({ createdAt: { $gt: ISODate("2026-01-01") } })

// Query: status == "pending" AND createdAt in range, sorted by amount
// ESR order: equality (status) → sort (amount) → range (createdAt)
db.orders.createIndex({ status: 1, amount: 1, createdAt: 1 })

// See what MongoDB does for a query
db.orders.find({ user_id: "abc" }).explain("executionStats")

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",          // or "COLLSCAN" = full scan (bad)
      "inputStage": {
        "stage": "IXSCAN",       // index scan (good)
        "indexName": "user_id_1"
      }
    }
  },
  "executionStats": {
    "totalDocsExamined": 5,      // low = good
    "totalDocsReturned": 5,
    "executionTimeMillis": 0,    // execution time
    "nReturned": 5
  }
}

// Create a text index on one or more string fields
db.articles.createIndex({ title: "text", body: "text" })

// Or index all string fields with a wildcard
db.articles.createIndex({ "$**": "text" })

// Search
db.articles.find({ $text: { $search: "mongodb index performance" } })

// Rank by relevance score
db.articles.find(
  { $text: { $search: "mongodb index" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })

// Store coordinates as GeoJSON
db.places.insertOne({
  name: "Brandenburg Gate",
  location: {
    type: "Point",
    coordinates: [13.3777, 52.5163]  // [longitude, latitude]
  }
})

// Create 2dsphere index for GeoJSON
db.places.createIndex({ location: "2dsphere" })

// Find places within 1 km of a point
db.places.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [13.3777, 52.5163] },
      $maxDistance: 1000  // meters
    }
  }
})

// Index only active users
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: { $eq: true } } }
)

// Index only orders that aren't completed
db.orders.createIndex(
  { user_id: 1, createdAt: -1 },
  { partialFilterExpression: { status: { $ne: "completed" } } }
)

// Index only documents that have a phone field
db.users.createIndex({ phone: 1 }, { sparse: true })

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

// See index statistics (usage counts)
db.orders.aggregate([{ $indexStats: {} }])

import mongoose, { Schema } from "mongoose";

const orderSchema = new Schema({
  user_id:   { type: Schema.Types.ObjectId, ref: "User", required: true },
  status:    { type: String, required: true },
  amount:    { type: Number, required: true },
  createdAt: { type: Date, default: Date.now },
});

// Compound index in Mongoose schema
orderSchema.index({ user_id: 1, createdAt: -1 });
orderSchema.index({ status: 1 }, { partialFilterExpression: { status: { $ne: "completed" } } });

const Order = mongoose.model("Order", orderSchema);

// Check query performance (wrap in async function for compatibility)
async function checkPerformance() {
  const userId = new mongoose.Types.ObjectId("64b1234567890abcdef12345");
  const explain = await Order
    .find({ user_id: userId })
    .sort({ createdAt: -1 })
    .explain("executionStats");

  console.log(explain.executionStats.executionTimeMillis);
}

from pymongo import MongoClient, ASCENDING, DESCENDING, TEXT

client = MongoClient("mongodb://admin:password@localhost:27017/")
db = client["myapp"]

# Create compound index
db.orders.create_index([("user_id", ASCENDING), ("created_at", DESCENDING)])

# Create text index
db.articles.create_index([("title", TEXT), ("body", TEXT)])

# Create geospatial index
db.places.create_index([("location", "2dsphere")])

# Analyze query
explain = db.orders.find({"user_id": "abc"}).explain()
print(explain["executionStats"]["executionTimeMillis"])

# List indexes
for index in db.orders.list_indexes():
    print(index["name"], index.get("key"))