Single-field index and composite index

Single-Field Indexes

Single-field indexes are the most basic type of index in MongoDB, created on a single field within a collection. When queries include only this field, such indexes can significantly improve query performance.

// Example of creating a single-field index
db.users.createIndex({ username: 1 })

This index stores the values of the username field in sorted order, where 1 indicates ascending and -1 indicates descending. It is effective for both equality and range queries:

// Equality query
db.users.find({ username: "john_doe" })

// Range query
db.users.find({ username: { $gt: "a", $lt: "m" } })

Characteristics of single-field indexes:

• Low creation and maintenance cost
• Suitable for frequently queried fields
• Supports sorting operations
• Can significantly reduce collection scans

Compound Indexes

Compound indexes are created on multiple fields, and the order of fields has a significant impact on index efficiency. MongoDB stores data according to the order of the indexed fields.

// Example of creating a compound index
db.orders.createIndex({ customerId: 1, orderDate: -1 })

Compound indexes follow the "leftmost prefix" principle, meaning queries must include the leftmost field of the index to utilize it:

// Queries that can use the index
db.orders.find({ customerId: "12345" })
db.orders.find({ customerId: "12345", orderDate: { $lt: ISODate("2023-01-01") } })

// Queries that cannot use the index
db.orders.find({ orderDate: { $lt: ISODate("2023-01-01") } })

Advantages of compound indexes:

• Supports multi-field joint queries
• Can cover more query patterns
• Meets both query and sorting requirements
• Reduces the overhead of multiple single-field indexes

Index Selection Strategy

Choosing between single-field and compound indexes depends on the query pattern. Single-field indexes suffice for simple queries, while complex queries require compound indexes.

// Example of query analysis
db.products.find({
  category: "electronics",
  price: { $lt: 1000 },
  rating: { $gt: 4 }
}).sort({ createdAt: -1 })

// The optimal index might be
db.products.createIndex({ 
  category: 1, 
  price: 1, 
  rating: 1, 
  createdAt: -1 
})

Factors to consider:

1. Frequently queried fields
2. Field selectivity (prioritize high-cardinality fields)
3. Sorting requirements
4. Memory constraints
5. Impact on write performance

Index Optimization Techniques

In practice, performance can be optimized with the following techniques:

1. Use covered queries to avoid table lookups:

// Create a covered index
db.customers.createIndex({ email: 1, name: 1 })

// Query returns only indexed fields
db.customers.find({ email: "user@example.com" }, { _id: 0, email: 1, name: 1 })

2. Index intersection optimization:

// Cases where two single-field indexes may be more efficient than one compound index
db.logs.createIndex({ userId: 1 })
db.logs.createIndex({ action: 1 })

// Query may use index intersection
db.logs.find({ userId: "123", action: "login" })

3. Partial indexes to reduce index size:

// Create an index only for active users
db.users.createIndex(
  { username: 1 },
  { partialFilterExpression: { isActive: true } }
)

Common Issues and Solutions

1. Index size inflation:

• Use TTL indexes to automatically clean up expired data
• Consider partial indexes
• Rebuild indexes periodically

2. Write performance degradation:

• Evaluate actual index usage
• Remove unused indexes
• Create indexes in bulk during off-peak hours

3. Insufficient memory:

• Prioritize keeping indexes for common queries in memory
• Use compressed storage engines
• Consider sharding clusters to distribute load

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

Real-World Case Studies

E-commerce platform product query optimization:

// Original query
db.products.find({
  category: "phones",
  brand: "Apple",
  price: { $gte: 500, $lte: 1000 },
  inStock: true
}).sort({ popularity: -1 }).limit(20)

// Optimized index
db.products.createIndex({
  category: 1,
  brand: 1,
  price: 1,
  inStock: 1,
  popularity: -1
})

// Further optimized as a partial index
db.products.createIndex({
  category: 1,
  brand: 1,
  price: 1,
  popularity: -1
}, {
  partialFilterExpression: { inStock: true }
})

Log system query optimization:

// Log query pattern
db.logs.find({
  app: "payment",
  level: "error",
  timestamp: { $gte: ISODate("2023-01-01"), $lte: ISODate("2023-01-02") }
}).sort({ timestamp: -1 })

// Optimal index design
db.logs.createIndex({ 
  app: 1,
  level: 1,
  timestamp: -1 
})

// Consider sharding strategy
sh.shardCollection("db.logs", { app: 1, timestamp: -1 })