Indexes

Basic Index Operations

Create Index

// Single field index
db.users.createIndex({ email: 1 })

// Compound index
db.users.createIndex({ firstName: 1, lastName: 1 })

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

// Sparse index
db.users.createIndex({ phone: 1 }, { sparse: true })

// Text index
db.users.createIndex({ name: "text", description: "text" })

// Geospatial index
db.users.createIndex({ location: "2dsphere" })

// TTL index
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

List Indexes

// Show all indexes
db.users.getIndexes()

// Show index details
db.users.getIndexSpecs()

// Show index stats
db.users.getIndexStats()

Drop Index

// Drop by name
db.users.dropIndex("email_1")

// Drop by specification
db.users.dropIndex({ email: 1 })

// Drop all indexes (except _id)
db.users.dropIndexes()

Index Types

Single Field Index

// Ascending index
db.users.createIndex({ age: 1 })

// Descending index
db.users.createIndex({ createdAt: -1 })

// On nested field
db.users.createIndex({ "profile.firstName": 1 })

// On array field
db.users.createIndex({ tags: 1 })

Compound Index

// Multiple fields
db.users.createIndex({ lastName: 1, firstName: 1 })

// Mixed order
db.users.createIndex({ category: 1, price: -1 })

// With nested fields
db.users.createIndex({ 
    "profile.city": 1, 
    "profile.state": 1 
})

// Compound with array
db.users.createIndex({ 
    category: 1, 
    tags: 1 
})

Unique Index

// Unique single field
db.users.createIndex({ email: 1 }, { unique: true })

// Unique compound
db.users.createIndex({ 
    username: 1, 
    domain: 1 
}, { unique: true })

// Unique sparse
db.users.createIndex({ 
    phone: 1 
}, { 
    unique: true, 
    sparse: true 
})

// Partial unique
db.users.createIndex({ email: 1 }, {
    unique: true,
    partialFilterExpression: { email: { $exists: true } }
})

Sparse Index

// Sparse index (skips null/missing values)
db.users.createIndex({ phone: 1 }, { sparse: true })

// Sparse unique
db.users.createIndex({ email: 1 }, { 
    unique: true, 
    sparse: true 
})

// Sparse compound
db.users.createIndex({ 
    phone: 1, 
    fax: 1 
}, { sparse: true })

Text Index

// Single field text index
db.products.createIndex({ description: "text" })

// Multiple fields text index
db.products.createIndex({ 
    name: "text", 
    description: "text",
    tags: "text"
})

// Text index with weights
db.products.createIndex({ 
    name: "text", 
    description: "text" 
}, {
    weights: {
        name: 10,
        description: 5
    }
})

// Text index with language
db.products.createIndex({ description: "text" }, {
    default_language: "english"
})

Geospatial Index

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

// 2d index
db.places.createIndex({ location: "2d" })

// GeoHaystack index
db.places.createIndex({ 
    location: "geoHaystack", 
    category: 1 
}, { bucketSize: 1 })

TTL Index

// TTL index with expiration
db.sessions.createIndex({ 
    createdAt: 1 
}, { 
    expireAfterSeconds: 3600 
})

// TTL on specific time
db.logs.createIndex({ 
    timestamp: 1 
}, { 
    expireAfterSeconds: 86400 
})

// TTL with partial filter
db.tempData.createIndex({ 
    createdAt: 1 
}, { 
    expireAfterSeconds: 1800,
    partialFilterExpression: { 
        type: "temporary" 
    }
})

Partial Index

// Partial index with filter
db.users.createIndex({ email: 1 }, {
    partialFilterExpression: { 
        status: "active" 
    }
})

// Partial index with conditions
db.orders.createIndex({ orderDate: 1 }, {
    partialFilterExpression: { 
        amount: { $gt: 100 } 
    }
})

// Partial index with multiple conditions
db.products.createIndex({ price: 1 }, {
    partialFilterExpression: {
        $and: [
            { category: "electronics" },
            { inStock: true }
        ]
    }
})

Wildcard Index

// Wildcard index on all fields
db.users.createIndex({ "$**": 1 })

// Wildcard index on specific path
db.users.createIndex({ "profile.$**": 1 })

// Wildcard index with projection
db.users.createIndex({ "$**": 1 }, {
    wildcardProjection: {
        "profile": 1,
        "metadata": 1,
        "password": 0
    }
})

Index Options

Basic Options

// Background index creation
db.users.createIndex({ email: 1 }, { background: true })

// Index with name
db.users.createIndex({ email: 1 }, { name: "email_index" })

// Index with collation
db.users.createIndex({ name: 1 }, {
    collation: { locale: "en", strength: 2 }
})

// Index with storage engine
db.users.createIndex({ email: 1 }, {
    storageEngine: { wiredTiger: { configString: "block_compressor=zlib" } }
})

Advanced Options

// Index with custom name
db.users.createIndex({ email: 1 }, { 
    name: "user_email_unique_index" 
})

// Index with comment
db.users.createIndex({ email: 1 }, { 
    name: "email_index",
    comment: "Index for email lookups"
})

// Index with hidden option
db.users.createIndex({ email: 1 }, { 
    hidden: true 
})

Index Management

Check Index Usage

// Get index usage stats
db.users.aggregate([
    { $indexStats: {} }
])

// Get index details
db.users.getIndexes()

// Check index size
db.users.stats().indexSizes

Analyze Index Performance

// Explain query with index
db.users.find({ email: "[e-mail]" }).explain("executionStats")

// Explain aggregation with index
db.users.aggregate([
    { $match: { age: { $gte: 25 } } }
]).explain("executionStats")

// Check if index is used
db.users.find({ email: "[e-mail]" }).explain("queryPlanner")

Index Maintenance

// Rebuild index
db.users.reIndex()

// Validate index
db.users.validate({ full: true })

// Compact collection (reduces index size)
db.runCommand({ compact: "users" })

Index Optimization

Query Optimization

// Covered query (index only)
db.users.find(
    { email: "[e-mail]" },
    { _id: 0, email: 1 }
)

// Compound index for range queries
db.users.createIndex({ 
    lastName: 1, 
    firstName: 1, 
    age: 1 
})

// Index for sorting
db.users.createIndex({ 
    category: 1, 
    price: -1 
})

Index Design Patterns

Equality, Sort, Range (ESR)

// Good index design
db.orders.createIndex({ 
    status: 1,        // Equality
    orderDate: -1,    // Sort
    amount: 1         // Range
})

// Query pattern
db.orders.find({
    status: "pending",
    amount: { $gt: 100 }
}).sort({ orderDate: -1 })

Compound Index Order

// Most selective first
db.users.createIndex({ 
    email: 1,         // High selectivity
    status: 1,        // Medium selectivity
    createdAt: 1      // Low selectivity
})

Index for Aggregation

// Index for aggregation pipeline
db.orders.createIndex({ 
    status: 1, 
    orderDate: 1 
})

// Aggregation query
db.orders.aggregate([
    { $match: { status: "completed" } },
    { $group: { 
        _id: { $dateToString: { format: "%Y-%m", date: "$orderDate" } },
        total: { $sum: "$amount" }
    }},
    { $sort: { _id: 1 } }
])

Common Index Patterns

User Management

// User lookup by email
db.users.createIndex({ email: 1 }, { unique: true })

// User search by name
db.users.createIndex({ 
    firstName: 1, 
    lastName: 1 
})

// User by location
db.users.createIndex({ 
    "profile.city": 1, 
    "profile.state": 1 
})

// User by status and date
db.users.createIndex({ 
    status: 1, 
    createdAt: -1 
})

E-commerce

// Product search
db.products.createIndex({ 
    name: "text", 
    description: "text" 
})

// Product by category and price
db.products.createIndex({ 
    category: 1, 
    price: 1 
})

// Product availability
db.products.createIndex({ 
    inStock: 1, 
    category: 1 
})

// Product ratings
db.products.createIndex({ 
    "ratings.average": -1, 
    category: 1 
})

Order Management

// Order by customer
db.orders.createIndex({ customerId: 1 })

// Order by status and date
db.orders.createIndex({ 
    status: 1, 
    orderDate: -1 
})

// Order by amount range
db.orders.createIndex({ 
    amount: 1, 
    orderDate: -1 
})

// Order lookup by number
db.orders.createIndex({ orderNumber: 1 }, { unique: true })

Analytics

// Time series data
db.events.createIndex({ 
    timestamp: -1, 
    eventType: 1 
})

// User activity
db.activities.createIndex({ 
    userId: 1, 
    activityDate: -1 
})

// Metrics aggregation
db.metrics.createIndex({ 
    metricName: 1, 
    timestamp: -1 
})

Index Best Practices

Performance Tips

// Use background indexing for large collections
db.largeCollection.createIndex({ field: 1 }, { background: true })

// Create indexes before data insertion
db.users.createIndex({ email: 1 }, { background: true })

// Monitor index usage
db.users.aggregate([{ $indexStats: {} }])

// Remove unused indexes
db.users.dropIndex("unused_index_name")

Index Limitations

// Maximum index key size: 1024 bytes
// Maximum compound index fields: 32
// Maximum indexes per collection: 64
// Text indexes: only one per collection
// Geospatial indexes: only one per collection

Index Sizing

// Check index size
db.users.stats().indexSizes

// Check total index size
db.users.stats().totalIndexSize

// Check index build progress
db.currentOp({ "createIndexes": { $exists: true } })

Troubleshooting Indexes

Common Issues

// Index too large
db.users.createIndex({ largeField: 1 })  // May fail if > 1024 bytes

// Duplicate key error
db.users.createIndex({ email: 1 }, { unique: true })
// Insert duplicate email will fail

// Sparse index with null values
db.users.createIndex({ phone: 1 }, { sparse: true })
// Documents without phone field won't be indexed

// Text index limitations
db.products.createIndex({ name: "text" })
// Only one text index per collection

Performance Issues

// Slow queries - check if index is used
db.users.find({ email: "[e-mail]" }).explain("executionStats")

// Large index scans - add more specific indexes
db.users.createIndex({ status: 1, email: 1 })

// Index not used - check query pattern
// Ensure query matches index order and fields