mongodb-query-and-index-optimize_skill

Overview

This skill helps optimize MongoDB queries and indexing strategies to reduce slow operations and improve throughput. It consolidates prioritized rules and practical instructions for creating indexes, tuning aggregation pipelines, and diagnosing performance issues. Use it to get concrete recommendations, verification commands, and safe-change workflows before touching production data.

How this skill works

It inspects query patterns, explain() output, profiler logs, and index usage statistics to identify missing or misordered indexes and inefficient pipeline stages. It applies a priority-driven rule set (Index Essentials, Specialized Indexes, Query Patterns, Aggregation Optimization, Performance Diagnostics) and produces actionable commands plus verification steps. The skill will propose index definitions, pipeline rewrites, or hints and will require explicit approval before issuing any write or destructive operations.

When to use it

• When a query shows COLLSCAN or high execution time in explain() or profiler
• When designing or reviewing compound, partial, TTL, text, or geospatial indexes
• When building or optimizing aggregation pipelines (use $match/$project early)
• When you see Performance Advisor or $indexStats suggesting unused or missing indexes
• When implementing pagination, full-text search, or array/geo queries

Best practices

• Follow the ESR ordering: Equality → Sort → Range for compound indexes
• Prefer covered queries by projecting only needed fields to avoid document fetches
• Use partial/sparse/TTL indexes to reduce index size and keep working sets in RAM
• Avoid skip-based pagination; use range-based pagination or indexed cursors
• Verify every recommendation with explain(), $indexStats, or the profiler before applying changes

Example use cases

• Convert a COLLSCAN into an indexed lookup by creating a compound index matching equality and sort fields
• Optimize an aggregation by moving $match and $project to the pipeline start and enabling allowDiskUse when necessary
• Add a partial index for session documents older than X to reduce index size and speed queries
• Diagnose why an $or query is not using an index and add matching indexes for each clause
• Use $indexStats to find and safely hide/remove unused indexes after testing