Agent skill

mongodb-index-creation

Master MongoDB index creation and types. Learn single-field, compound, unique, text, geospatial, and TTL indexes. Optimize query performance dramatically with proper indexing.

View SKILL.md on GitHub Repository
Stars 1
Forks 0

Install this agent skill to your Project

npx add-skill https://github.com/pluginagentmarketplace/custom-plugin-mongodb/tree/main/skills/mongodb-index-creation

SKILL.md

MongoDB Index Creation & Types

Dramatically improve query performance with strategic indexing.

Quick Start

Create Indexes

javascript
// Single field index
await collection.createIndex({ email: 1 })

// Compound index (order matters!)
await collection.createIndex({ status: 1, createdAt: -1 })

// Unique index
await collection.createIndex({ email: 1 }, { unique: true })

// Sparse index (skip null values)
await collection.createIndex({ phone: 1 }, { sparse: true })

// TTL index (auto-delete after 24 hours)
await collection.createIndex({ createdAt: 1 }, { expireAfterSeconds: 86400 })

// Text index (full-text search)
await collection.createIndex({ title: 'text', content: 'text' })

// Geospatial index
await collection.createIndex({ location: '2dsphere' })

Index Types

Single Field Index

javascript
// Simple index for one field
db.users.createIndex({ email: 1 })

// Benefits:
// - Speeds up queries on email field
// - Speeds up sorts on email
// - Speeds up range queries: { $gt, $lt }

// When to use:
// - Frequently filtered/sorted field
// - High cardinality (many unique values)

Compound Index

javascript
// Multiple fields - ORDER MATTERS!
db.orders.createIndex({ status: 1, createdAt: -1 })

// Good for queries like:
// { status: 'completed', createdAt: {$gt: date} }
// { status: 'completed' } // Can use this index

// Bad for:
// { createdAt: {$gt: date} } // Won't use index well

Unique Index

javascript
// Ensure field uniqueness
db.users.createIndex({ email: 1 }, { unique: true })

// Prevents duplicates:
// - insertOne with duplicate email → ERROR
// - Can't insert if email already exists

// Sparse unique (allow multiple nulls)
db.users.createIndex({ phone: 1 }, { unique: true, sparse: true })

Sparse Index

javascript
// Skip documents where field is null/missing
db.users.createIndex({ phone: 1 }, { sparse: true })

// Benefits:
// - Smaller index (excludes nulls)
// - Matches queries with { $exists: true }

// When to use:
// - Optional fields (not all documents have it)
// - Reduce index size

Text Index

javascript
// Full-text search
db.articles.createIndex({
  title: 'text',
  content: 'text',
  tags: 'text'
})

// Query:
db.articles.find({ $text: { $search: 'mongodb' } })

// Weights (title more important than content)
db.articles.createIndex({
  title: 'text',
  content: 'text'
}, { weights: { title: 10, content: 5 } })

Geospatial Index

javascript
// 2D spherical (lat/long)
db.venues.createIndex({ location: '2dsphere' })

// Query nearby
db.venues.find({
  location: {
    $near: {
      type: 'Point',
      coordinates: [-73.97, 40.77]
    },
    $maxDistance: 5000  // 5km
  }
})

TTL Index

javascript
// Auto-delete documents after time period
db.sessions.createIndex({ createdAt: 1 }, {
  expireAfterSeconds: 3600  // 1 hour
})

// Use cases:
// - Sessions that expire
// - Temporary logs
// - Cache-like collections

// MongoDB checks once per minute
// Deletion might lag up to 1 minute

Index Management

List Indexes

javascript
// Show all indexes
const indexes = await collection.indexes()
console.log(indexes)

// Shows: name, key, size, doc count

Drop Index

javascript
// Drop specific index
await collection.dropIndex('email_1')

// Drop all non-_id indexes
await collection.dropIndexes()

Index Options

javascript
await collection.createIndex({ email: 1 }, {
  unique: true,           // Enforce uniqueness
  sparse: true,          // Skip nulls
  background: true,      // Don't block writes
  expireAfterSeconds: 86400, // TTL
  collation: { locale: 'en' }, // Language-specific
  name: 'custom_name'    // Custom index name
})

Index Design: ESR Rule

Equality, Sort, Range - optimal compound index order

javascript
// Query: Find active users sorted by created date, age 18-65
db.users.find({
  status: 'active',          // Equality
  age: { $gte: 18, $lte: 65 } // Range
}).sort({ createdAt: -1 })     // Sort

// Optimal index:
db.users.createIndex({
  status: 1,         // Equality first
  createdAt: -1,    // Sort second
  age: 1            // Range last
})

Covered Queries

Make Query "Covered"

javascript
// Query returns entirely from index, no documents fetched!

// Create index with all needed fields
db.users.createIndex({ email: 1, name: 1, age: 1 })

// Query (covered - no docs fetched)
db.users.find(
  { email: 'user@example.com' },
  { projection: { email: 1, name: 1, age: 1, _id: 0 } }
)

// Much faster than fetching documents!

Monitoring Indexes

Check Index Usage

javascript
// MongoDB 4.4+
db.collection.aggregate([
  { $indexStats: {} }
])

// Shows:
// - accesses.ops: Number of operations using index
// - accesses.since: When index was created

Remove Unused Indexes

javascript
// Identify unused:
// - Low accesses.ops value
// - Recent accesses.since date

// Drop unused indexes:
await collection.dropIndex('unused_index_name')

Best Practices

✅ Index Design:

  1. Index for queries - Add on frequently filtered fields
  2. Use ESR rule - Equality, Sort, Range order
  3. Avoid over-indexing - Each index has cost
  4. Monitor index size - Large indexes need memory
  5. Test impact - Measure query performance

✅ Performance:

  1. Create indexes early - Before data growth
  2. Use explain() - Verify index usage (IXSCAN)
  3. Batch index creation - If many needed
  4. Monitor index stats - Remove unused
  5. Plan for growth - Future query patterns

❌ Avoid:

  1. Index everything - Wastes storage and memory
  2. Complex regex without index - Always slow
  3. Sorting without index - Memory-intensive
  4. Large text indexes - Memory usage
  5. Outdated indexes - Remove when no longer needed

Next Steps

  1. Identify slow queries - Use explain()
  2. Create strategic indexes - For your access patterns
  3. Monitor performance - Before/after metrics
  4. Optimize compound indexes - ESR rule
  5. Remove unused indexes - Keep lean

Ready to speed up your MongoDB! ⚡

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

pluginagentmarketplace/custom-plugin-mongodb

mongodb-find-queries

Master MongoDB find queries with filters, projections, sorting, and pagination. Learn query operators, comparison, logical operators, and real-world query patterns. Use when retrieving data from MongoDB collections.

1 0
Explore
pluginagentmarketplace/custom-plugin-mongodb

mongodb-atlas-setup

Master MongoDB Atlas cloud setup, cluster configuration, security, networking, backups, and monitoring. Get production-ready cloud database in minutes. Use when setting up cloud MongoDB, configuring clusters, or managing Atlas.

1 0
Explore
pluginagentmarketplace/custom-plugin-mongodb

mongodb-replication-sharding

Master MongoDB replication, replica sets, and sharding for distributed deployments. Learn failover, shard keys, and cluster management. Use when setting up high availability or scaling horizontally.

1 0
Explore
pluginagentmarketplace/custom-plugin-mongodb

mongodb-crud-operations

Master MongoDB CRUD operations, document insertion, querying, updating, and deletion. Learn BSON format, ObjectId, data types, and basic operations. Use when working with documents, collections, and fundamental MongoDB operations.

1 0
Explore
pluginagentmarketplace/custom-plugin-mongodb

mongodb-indexing-optimization

Master MongoDB indexing and query optimization. Learn index types, explain plans, performance tuning, and query analysis. Use when optimizing slow queries, analyzing performance, or designing indexes.

1 0
Explore
pluginagentmarketplace/custom-plugin-mongodb

mongodb-schema-design

Master MongoDB schema design and data modeling patterns. Learn embedding vs referencing, relationships, normalization, and schema evolution. Use when designing databases, normalizing data, or optimizing queries.

1 0
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results