Performance optimization for complex queries

Understanding Performance Bottlenecks in Complex Queries

Mongoose can encounter performance issues when handling complex queries, especially with large datasets. Typical bottlenecks include excessive database round trips, inefficient index usage, in-memory processing of large data volumes, and the N+1 query problem. For example, a common scenario involves fetching all active users from the user collection and then retrieving each user's order information:

const users = await User.find({ active: true });
const usersWithOrders = await Promise.all(
  users.map(async user => {
    const orders = await Order.find({ userId: user._id });
    return { ...user.toObject(), orders };
  })
);

This approach leads to the N+1 query problem, putting significant pressure on the database.

Basic Query Optimization Strategies

The most fundamental optimization is to use projection to fetch only necessary fields. Mongoose's select method allows precise control over returned fields:

// Fetch only name and email fields
const users = await User.find({ active: true }).select('name email');

Another key strategy is using lean() queries, which skip Mongoose document instantiation and return plain JavaScript objects:

const users = await User.find({ active: true }).lean();

For pagination scenarios, cursor-based pagination should be preferred over offset-based pagination:

// Using cursor pagination
const users = await User.find({ _id: { $gt: lastId } })
  .limit(10)
  .sort({ _id: 1 });

Advanced Query Optimization Techniques

Aggregation Pipeline Optimization

Mongoose's aggregate() method provides powerful data processing capabilities but requires special attention to performance:

const result = await Order.aggregate([
  { $match: { status: 'completed' } },
  { $group: { 
    _id: '$userId', 
    total: { $sum: '$amount' } 
  }},
  { $sort: { total: -1 } },
  { $limit: 10 }
]);

Key optimization points include:

1. Using $match early to reduce document processing
2. Properly using $project to limit fields
3. Applying $limit before memory-intensive stages

Batch Operations and Bulk Fetching

When using populate, Mongoose by default executes separate queries for each referenced document. This can be optimized with options:

const users = await User.find()
  .populate({
    path: 'orders',
    options: { batchSize: 100 }  // Control batch size
  });

For complex associations, consider implementing manual batch fetching:

const users = await User.find().lean();
const userIds = users.map(u => u._id);
const orders = await Order.find({ userId: { $in: userIds } });

const usersWithOrders = users.map(user => {
  const userOrders = orders.filter(o => o.userId.equals(user._id));
  return { ...user, orders: userOrders };
});

Indexing Strategies and Query Analysis

Proper index design is crucial for query performance. Use explain() to analyze query execution plans:

const explanation = await User.find({ email: /@example\.com$/ })
  .explain('executionStats');

Common indexing strategies include:

1. Creating indexes for high-frequency query fields
2. Paying attention to field order in compound indexes
3. Using full-text indexes for text searches

// Create compound index
User.schema.index({ active: 1, createdAt: -1 });

// Text index
User.schema.index({ name: 'text', bio: 'text' });

Caching and Read/Write Separation

For frequently accessed but infrequently updated data, consider adding a caching layer:

async function getTopUsers() {
  const cacheKey = 'top:users';
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
  
  const users = await User.find().sort({ score: -1 }).limit(10);
  await redis.setex(cacheKey, 3600, JSON.stringify(users));
  return users;
}

For read-heavy scenarios, consider read/write separation:

// Use secondary nodes for read operations
const users = await User.find().read('secondary');

Optimizing Complex Conditional Queries

When handling multi-condition queries, pay attention to condition order and combination:

// Not recommended - inefficient condition order
const users = await User.find({
  $or: [
    { status: 'vip', lastLogin: { $gt: weekAgo } },
    { points: { $gt: 1000 } }
  ]
});

// Optimized version - place high-selectivity conditions first
const users = await User.find({
  $or: [
    { lastLogin: { $gt: weekAgo }, status: 'vip' },
    { points: { $gt: 1000 } }
  ]
});

For geospatial queries, ensure appropriate geospatial indexes exist:

// Create 2dsphere index
User.schema.index({ location: '2dsphere' });

// Geospatial query
const nearbyUsers = await User.find({
  location: {
    $near: {
      $geometry: { type: 'Point', coordinates: [longitude, latitude] },
      $maxDistance: 1000
    }
  }
});

Transaction and Bulk Write Optimization

Mongoose supports transactions but requires performance awareness:

const session = await mongoose.startSession();
try {
  session.startTransaction();
  
  await User.updateOne(
    { _id: userId },
    { $inc: { balance: -amount } },
    { session }
  );
  
  await Order.create(
    [{ userId, amount, product }],
    { session }
  );
  
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

Prefer bulkWrite for batch operations:

await User.bulkWrite([
  { updateOne: {
    filter: { _id: user1Id },
    update: { $inc: { score: 10 } }
  }},
  { updateOne: {
    filter: { _id: user2Id },
    update: { $inc: { score: 5 } }
  }},
  { insertOne: {
    document: { name: 'New User', email: 'new@example.com' }
  }}
]);

Monitoring and Continuous Optimization

Establishing query performance monitoring is crucial. Use Mongoose's debugging features and slow query logs:

// Enable Mongoose query logging
mongoose.set('debug', function(collectionName, method, query, doc) {
  logger.debug(`${collectionName}.${method}`, JSON.stringify(query));
});

// Set slow query threshold (milliseconds)
mongoose.set('slowQueryThreshold', 200);

Regularly analyze query patterns and adjust indexes:

// Get collection index information
const indexes = await User.collection.getIndexes();

// Drop unused indexes
await User.collection.dropIndex('old_index_name');