MongoDB Indexing

Index Types

MongoDB supports various index types for different use cases. Choose the right index type based on your query patterns and data structure.

db.users.createIndex({ email: 1 })

db.users.createIndex({ city: 1, age: -1 })

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

db.posts.createIndex({ content: "text" })

🔹 Single Field Indexes

Single field indexes are the most basic type. They index one field in ascending (1) or descending (-1) order.

🔸 Create Single Field Index

// Index on email field (ascending)
db.users.createIndex({ email: 1 })

// Index on age field (descending)
db.users.createIndex({ age: -1 })

// Index on nested field
db.users.createIndex({ "address.city": 1 })

Without Index: Scans 100,000 documents
With Index: Scans 1 document
Speed Improvement: 100,000x faster!

🔹 Compound Indexes

Compound indexes index multiple fields together. The order of fields matters - queries can use the index from left to right.

🔸 Create Compound Index

// Index on city and age
db.users.createIndex({ city: 1, age: -1 })

// Index on category, price, and rating
db.products.createIndex({ 
    category: 1, 
    price: -1, 
    rating: -1 
})

🔸 Query Examples

// These queries use the index { city: 1, age: -1 }
db.users.find({ city: "New York" })
db.users.find({ city: "New York", age: { $gt: 25 } })

// This query does NOT use the index efficiently
db.users.find({ age: { $gt: 25 } })

🔹 Unique Indexes

Unique indexes ensure that indexed fields don't contain duplicate values. MongoDB rejects documents with duplicate values for the indexed field.

🔸 Create Unique Index

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

// Compound unique index
db.users.createIndex(
    { username: 1, provider: 1 },
    { unique: true }
)

// Unique index on array field
db.products.createIndex({ sku: 1 }, { unique: true })

// First insert succeeds
db.users.insertOne({ email: "[email protected]", name: "John" })

// Second insert fails (duplicate email)
db.users.insertOne({ email: "[email protected]", name: "Jane" })
// Error: E11000 duplicate key error

🔹 Text Indexes

Text indexes support text search queries on string content. They tokenize and stem text, enabling full-text search capabilities.

🔸 Create Text Index

// Text index on single field
db.posts.createIndex({ content: "text" })

// Text index on multiple fields
db.articles.createIndex({
    title: "text",
    body: "text",
    tags: "text"
})

// Text index with weights
db.posts.createIndex(
    { title: "text", content: "text" },
    { weights: { title: 10, content: 5 } }
)

🔸 Search with Text Index

// Search for "mongodb tutorial"
db.posts.find({ $text: { $search: "mongodb tutorial" } })

// Search for exact phrase
db.posts.find({ $text: { $search: "\"mongodb tutorial\"" } })

// Exclude words with minus sign
db.posts.find({ $text: { $search: "mongodb -sql" } })

// Search posts about MongoDB
db.posts.find(
    { $text: { $search: "mongodb database" } },
    { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })

🔹 Multikey Indexes

Multikey indexes automatically index array fields. MongoDB creates separate index entries for each array element.

🔸 Create Multikey Index

// Index on array field
db.posts.createIndex({ tags: 1 })

// Query uses multikey index
db.posts.find({ tags: "mongodb" })
db.posts.find({ tags: { $in: ["mongodb", "database"] } })

// Document with array field
{ "_id": 1, "title": "Post", "tags": ["mongodb", "database", "nosql"] }

// Index creates entries for:
// "mongodb" -> document 1
// "database" -> document 1
// "nosql" -> document 1

🔹 Index Management

View, analyze, and remove indexes to optimize database performance.

🔸 View Indexes

// List all indexes on collection
db.users.getIndexes()

// View index sizes
db.users.stats().indexSizes

🔸 Drop Indexes

// Drop specific index
db.users.dropIndex("email_1")

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

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

🔸 Analyze Query Performance

// Explain query execution
db.users.find({ email: "[email protected]" }).explain("executionStats")

// Check if index is used
db.products.find({ 
    category: "Electronics",
    price: { $lt: 1000 }
}).explain("executionStats")

🔹 Index Properties

Customize index behavior with special properties.

🔸 Sparse Indexes

// Only index documents with the field
db.users.createIndex(
    { phone: 1 },
    { sparse: true }
)

🔸 TTL Indexes

// Auto-delete documents after 30 days
db.sessions.createIndex(
    { createdAt: 1 },
    { expireAfterSeconds: 2592000 }
)

🔸 Partial Indexes

// Index only active users
db.users.createIndex(
    { email: 1 },
    { 
        partialFilterExpression: { 
            status: "active" 
        } 
    }
)

🔹 Index Best Practices

Follow these guidelines for optimal index performance:

🔹 Practical Examples

Real-world indexing scenarios:

// Compound index for product filtering
db.products.createIndex({
    category: 1,
    price: 1,
    rating: -1
})

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

// Unique index for SKU
db.products.createIndex(
    { sku: 1 },
    { unique: true }
)

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

// Index for login queries
db.users.createIndex({ email: 1, password: 1 })

// TTL index for password reset tokens
db.resetTokens.createIndex(
    { createdAt: 1 },
    { expireAfterSeconds: 3600 }
)

// Compound index for user timeline
db.posts.createIndex({
    userId: 1,
    createdAt: -1
})

// Text index for post search
db.posts.createIndex({ content: "text" })

// Multikey index for hashtags
db.posts.createIndex({ hashtags: 1 })