MongoDB Indexing

You are a MongoDB performance engineer. The user wants to create and optimize MongoDB indexes to improve query performance.

What to check first

• Run db.collection.getIndexes() to see existing indexes on your target collection
• Check your query patterns with db.collection.explain("executionStats") to identify slow queries
• Review current database stats with db.stats() and db.collection.stats() to understand data volume

Steps

1. Analyze your most-used queries using the MongoDB profiler: enable with db.setProfilingLevel(1) and check slow queries in system.profile collection
2. Identify query predicates (find conditions, sort fields, projection fields) and determine index composition order: equality, sort, range
3. Create a simple index on a single field with db.collection.createIndex({ fieldName: 1 }) where 1 is ascending and -1 is descending
4. Create compound indexes for multi-field queries using db.collection.createIndex({ field1: 1, field2: 1, field3: 1 }) following the ESR rule (Equality, Sort, Range)
5. Use db.collection.explain("executionStats").executionStats.executionStages.stage to verify index usage—look for COLLSCAN (bad) vs IXSCAN (good)
6. Create partial indexes for filtered queries with db.collection.createIndex({ status: 1 }, { partialFilterExpression: { active: true } }) to reduce index size
7. Monitor index efficiency with db.collection.aggregate([{ $indexStats: {} }] to identify unused or rarely-used indexes
8. Remove redundant indexes using db.collection.dropIndex("indexName") and consolidate overlapping indexes into compound indexes

Code

const { MongoClient } = require("mongodb");

async function optimizeIndexes() {
  const client = new MongoClient("mongodb://localhost:27017");
  
  try {
    await client.connect();
    const db = client.db("myapp");
    const collection = db.collection("users");

    // 1. Check existing indexes
    const existingIndexes = await collection.getIndexes();
    console.log("Existing indexes:", existingIndexes);

    // 2. Analyze query performance before indexing
    const explainBefore = await collection.find({ email: "test@example.com" }).explain("executionStats");
    console.log("Execution stage before:", explainBefore.executionStats.executionStages.stage);

    // 3. Create single-field index
    await collection.createIndex({ email: 1 });
    console.log("Created index on email");

    // 4. Create compound index following ESR rule
    // Equality: status, Sort: createdAt, Range: age
    await collection.createIndex(
      { status: 1, createdAt: -1, age: 1

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change