mongodb-indexing_skill

Overview

This skill teaches MongoDB indexing and query optimization to improve read and write performance. It covers index types, ESR-based compound index design, explain plans, and practical tuning steps. Use it to diagnose slow queries, design efficient indexes, and reduce collection scans.

How this skill works

The skill inspects queries and collection schemas, recommends index types (single, compound, multikey, text, geospatial, TTL) and shows how to create or drop indexes using drivers like PyMongo. It explains explain() output and identifies IXSCAN vs COLLSCAN, covering queries, index intersection opportunities, and index size considerations. You get concrete examples and Python snippets to apply changes and verify improvements with executionStats.

When to use it

• When queries are slow or reports show high executionTimeMillis
• When designing new collections to support common query patterns
• Before deploying large-scale changes to read-heavy workloads
• When indexes consume too much memory or disks and need pruning
• When creating search, geospatial, or TTL-based lifecycle policies

Best practices

• Apply the ESR rule: Equality fields first, then Sort fields, then Range fields
• Use explain('executionStats') to confirm IXSCAN and check totalDocsExamined
• Prefer covering indexes for frequent read projections to avoid fetches
• Monitor index size and remove unused indexes to save RAM
• Avoid indexing low-cardinality or highly null fields; consider sparse or partial indexes
• Build compound indexes in the correct field order; consider index intersection only when necessary

Example use cases

• Optimize a login or lookup query by adding a single-field unique index on email
• Speed up paginated feeds by creating a compound index on status and createdAt
• Implement full-text search with text indexes for title and description fields
• Support geolocation features using 2dsphere indexes and $near queries
• Automatically expire session data using TTL indexes on createdAt