MongoDB Query Optimization and Performance Analysis: SQL-Style Database Tuning

-- Slow query example - finds recent orders for analytics
SELECT 
  o.order_id,
  o.customer_id,
  o.total_amount,
  o.status,
  o.created_at,
  c.name as customer_name,
  c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2025-08-01'
  AND o.status IN ('pending', 'processing', 'shipped')
  AND o.total_amount > 100
ORDER BY o.created_at DESC
LIMIT 50;

-- Performance problems:
-- - Full table scan on orders (millions of rows)
-- - JOIN operation on unindexed fields
-- - Complex filtering without proper indexes
-- - Sorting large result sets

// Slow aggregation pipeline
db.orders.aggregate([
  {
    $match: {
      created_at: { $gte: ISODate("2025-08-01") },
      status: { $in: ["pending", "processing", "shipped"] },
      total_amount: { $gt: 100 }
    }
  },
  {
    $lookup: {
      from: "customers",
      localField: "customer_id", 
      foreignField: "_id",
      as: "customer"
    }
  },
  {
    $unwind: "$customer"
  },
  {
    $project: {
      order_id: 1,
      customer_id: 1,
      total_amount: 1,
      status: 1,
      created_at: 1,
      customer_name: "$customer.name",
      customer_email: "$customer.email"
    }
  },
  {
    $sort: { created_at: -1 }
  },
  {
    $limit: 50
  }
]);

// Without proper indexes, this query may scan millions of documents

// Analyze query execution with explain()
const explainResult = db.orders.find({
  created_at: { $gte: ISODate("2025-08-01") },
  status: "pending",
  total_amount: { $gt: 100 }
}).sort({ created_at: -1 }).limit(10).explain("executionStats");

console.log(explainResult.executionStats);

-- SQL execution plan analysis concepts
EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE created_at >= '2025-08-01'
  AND status = 'pending' 
  AND total_amount > 100
ORDER BY created_at DESC
LIMIT 10;

-- Key metrics to analyze:
-- - Scan type (Index Scan vs Sequential Scan)
-- - Rows examined vs rows returned
-- - Execution time and buffer usage
-- - Join algorithms and sort operations

// MongoDB explain output structure
{
  "executionStats": {
    "executionSuccess": true,
    "totalDocsExamined": 2500000,    // Documents scanned
    "totalDocsReturned": 10,         // Documents returned
    "executionTimeMillis": 1847,     // Query execution time
    "totalKeysExamined": 0,          // Index keys examined
    "stage": "SORT",                 // Root execution stage
    "inputStage": {
      "stage": "SORT_KEY_GENERATOR",
      "inputStage": {
        "stage": "COLLSCAN",         // Collection scan (bad!)
        "direction": "forward",
        "docsExamined": 2500000,
        "filter": {
          "$and": [
            { "created_at": { "$gte": ISODate("2025-08-01") }},
            { "status": { "$eq": "pending" }},
            { "total_amount": { "$gt": 100 }}
          ]
        }
      }
    }
  }
}

// Check available indexes
db.orders.getIndexes();

// Results show existing indexes:
[
  { "v": 2, "key": { "_id": 1 }, "name": "_id_" },
  { "v": 2, "key": { "customer_id": 1 }, "name": "customer_id_1" },
  // Missing optimal indexes for our query
]

// Query hint to force specific index usage
db.orders.find({
  created_at: { $gte: ISODate("2025-08-01") },
  status: "pending"
}).hint({ created_at: 1, status: 1 });

-- Check index usage in SQL
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders';

-- Force index usage with hints
SELECT /*+ INDEX(orders idx_orders_created_status) */
  order_id, total_amount
FROM orders  
WHERE created_at >= '2025-08-01'
  AND status = 'pending';

// ESR Rule: Equality -> Sort -> Range
// Query: Find recent orders by status, sorted by date
db.orders.find({
  status: "pending",           // Equality
  created_at: { $gte: date }   // Range
}).sort({ created_at: -1 });   // Sort

// Optimal index design
db.orders.createIndex({
  status: 1,           // Equality fields first
  created_at: -1       // Sort/Range fields last, matching sort direction
});

-- SQL compound index design
CREATE INDEX idx_orders_status_created ON orders (
  status,              -- Equality condition
  created_at DESC      -- Sort field with direction
) 
WHERE status IN ('pending', 'processing', 'shipped');

-- Include additional columns for covering index
CREATE INDEX idx_orders_covering ON orders (
  status,
  created_at DESC
) INCLUDE (
  order_id,
  customer_id,
  total_amount
);

// Partial indexes for specific conditions
db.orders.createIndex(
  { created_at: -1, customer_id: 1 },
  { 
    partialFilterExpression: { 
      status: { $in: ["pending", "processing"] },
      total_amount: { $gt: 50 }
    }
  }
);

// Text indexes for search functionality
db.products.createIndex({
  name: "text",
  description: "text", 
  category: "text"
}, {
  weights: {
    name: 10,
    description: 5,
    category: 1
  }
});

// Sparse indexes for optional fields
db.customers.createIndex(
  { "preferences.newsletter": 1 },
  { sparse: true }
);

// TTL indexes for automatic document expiration
db.sessions.createIndex(
  { expires_at: 1 },
  { expireAfterSeconds: 0 }
);

// Geospatial indexes for location queries
db.stores.createIndex({ location: "2dsphere" });

// Index usage statistics
class IndexAnalyzer {
  constructor(db) {
    this.db = db;
  }

  async analyzeCollectionIndexes(collectionName) {
    const collection = this.db.collection(collectionName);

    // Get index statistics
    const indexStats = await collection.aggregate([
      { $indexStats: {} }
    ]).toArray();

    // Analyze each index
    const analysis = indexStats.map(stat => ({
      indexName: stat.name,
      usageCount: stat.accesses.ops,
      lastUsed: stat.accesses.since,
      keyPattern: stat.key,
      size: stat.size || 0,
      efficiency: this.calculateIndexEfficiency(stat)
    }));

    return {
      collection: collectionName,
      totalIndexes: analysis.length,
      unusedIndexes: analysis.filter(idx => idx.usageCount === 0),
      mostUsedIndexes: analysis
        .sort((a, b) => b.usageCount - a.usageCount)
        .slice(0, 5),
      recommendations: this.generateRecommendations(analysis)
    };
  }

  calculateIndexEfficiency(indexStat) {
    const opsPerDay = indexStat.accesses.ops / 
      Math.max(1, (Date.now() - indexStat.accesses.since) / (1000 * 60 * 60 * 24));

    return {
      opsPerDay: Math.round(opsPerDay),
      efficiency: opsPerDay > 100 ? 'high' : 
                 opsPerDay > 10 ? 'medium' : 'low'
    };
  }

  generateRecommendations(analysis) {
    const recommendations = [];

    // Find unused indexes
    const unused = analysis.filter(idx => 
      idx.usageCount === 0 && idx.indexName !== '_id_'
    );

    if (unused.length > 0) {
      recommendations.push({
        type: 'DROP_UNUSED_INDEXES',
        message: `Consider dropping ${unused.length} unused indexes`,
        indexes: unused.map(idx => idx.indexName)
      });
    }

    // Find duplicate key patterns
    const keyPatterns = new Map();
    analysis.forEach(idx => {
      const pattern = JSON.stringify(idx.keyPattern);
      if (keyPatterns.has(pattern)) {
        recommendations.push({
          type: 'DUPLICATE_INDEXES',
          message: 'Found potentially duplicate indexes',
          indexes: [keyPatterns.get(pattern), idx.indexName]
        });
      }
      keyPatterns.set(pattern, idx.indexName);
    });

    return recommendations;
  }
}

// Inefficient pipeline - filters late
const slowPipeline = [
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id", 
      as: "customer"
    }
  },
  {
    $unwind: "$customer"
  },
  {
    $match: {
      created_at: { $gte: ISODate("2025-08-01") },
      status: "completed",
      total_amount: { $gt: 100 }
    }
  },
  {
    $group: {
      _id: "$customer.region",
      total_revenue: { $sum: "$total_amount" },
      order_count: { $sum: 1 }
    }
  }
];

// Optimized pipeline - filters early
const optimizedPipeline = [
  {
    $match: {
      created_at: { $gte: ISODate("2025-08-01") },
      status: "completed", 
      total_amount: { $gt: 100 }
    }
  },
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
    }
  },
  {
    $unwind: "$customer"
  },
  {
    $group: {
      _id: "$customer.region",
      total_revenue: { $sum: "$total_amount" },
      order_count: { $sum: 1 }
    }
  }
];

-- SQL query optimization principles
-- Bad: JOIN before filtering
SELECT 
  c.region,
  SUM(o.total_amount) as total_revenue,
  COUNT(*) as order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  -- JOIN first
WHERE o.created_at >= '2025-08-01'                 -- Filter later
  AND o.status = 'completed'
  AND o.total_amount > 100
GROUP BY c.region;

-- Good: Filter before JOIN
SELECT 
  c.region,
  SUM(o.total_amount) as total_revenue,
  COUNT(*) as order_count  
FROM (
  SELECT customer_id, total_amount
  FROM orders 
  WHERE created_at >= '2025-08-01'    -- Filter early
    AND status = 'completed'
    AND total_amount > 100
) o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region;

// Check pipeline index usage
const pipelineExplain = db.orders.aggregate(optimizedPipeline, { 
  explain: true 
});

// Analyze stage-by-stage index usage
const stageAnalysis = pipelineExplain.stages.map((stage, index) => ({
  stageNumber: index,
  stageName: Object.keys(stage)[0],
  indexUsage: stage.$cursor ? stage.$cursor.queryPlanner : null,
  documentsExamined: stage.executionStats?.totalDocsExamined || 0,
  documentsReturned: stage.executionStats?.totalDocsReturned || 0
}));

console.log('Pipeline Index Analysis:', stageAnalysis);

// Pipeline with memory management
const memoryEfficientPipeline = [
  {
    $match: {
      created_at: { $gte: ISODate("2025-08-01") }
    }
  },
  {
    $sort: { created_at: 1 }  // Use index for sorting
  },
  {
    $group: {
      _id: {
        year: { $year: "$created_at" },
        month: { $month: "$created_at" },
        day: { $dayOfMonth: "$created_at" }
      },
      daily_revenue: { $sum: "$total_amount" },
      order_count: { $sum: 1 }
    }
  },
  {
    $sort: { "_id.year": -1, "_id.month": -1, "_id.day": -1 }
  }
];

// Enable allowDiskUse for large datasets
db.orders.aggregate(memoryEfficientPipeline, {
  allowDiskUse: true,
  maxTimeMS: 60000
});

class QueryPerformanceMonitor {
  constructor(db) {
    this.db = db;
    this.slowQueries = new Map();
    this.thresholds = {
      slowQueryMs: 100,
      examineToReturnRatio: 100,
      indexScanThreshold: 1000
    };
  }

  async enableProfiling() {
    // Enable database profiling for slow operations
    await this.db.admin().command({
      profile: 2,  // Profile all operations
      slowms: this.thresholds.slowQueryMs,
      sampleRate: 0.1  // Sample 10% of operations
    });
  }

  async analyzeSlowQueries() {
    const profilerCollection = this.db.collection('system.profile');

    const slowQueries = await profilerCollection.find({
      ts: { $gte: new Date(Date.now() - 3600000) }, // Last hour
      millis: { $gte: this.thresholds.slowQueryMs }
    }).sort({ ts: -1 }).limit(100).toArray();

    const analysis = slowQueries.map(query => ({
      timestamp: query.ts,
      duration: query.millis,
      namespace: query.ns,
      operation: query.op,
      command: query.command,
      docsExamined: query.docsExamined || 0,
      docsReturned: query.docsReturned || 0,
      planSummary: query.planSummary,
      executionStats: query.execStats,
      efficiency: this.calculateQueryEfficiency(query)
    }));

    return this.categorizePerformanceIssues(analysis);
  }

  calculateQueryEfficiency(query) {
    const examined = query.docsExamined || 0;
    const returned = query.docsReturned || 1;
    const ratio = examined / returned;

    return {
      examineToReturnRatio: Math.round(ratio),
      efficiency: ratio < 10 ? 'excellent' :
                 ratio < 100 ? 'good' : 
                 ratio < 1000 ? 'poor' : 'critical',
      usedIndex: query.planSummary && !query.planSummary.includes('COLLSCAN')
    };
  }

  categorizePerformanceIssues(queries) {
    const issues = {
      collectionScans: [],
      inefficientIndexUsage: [],
      largeResultSets: [],
      longRunningQueries: []
    };

    queries.forEach(query => {
      // Collection scans
      if (query.planSummary && query.planSummary.includes('COLLSCAN')) {
        issues.collectionScans.push(query);
      }

      // Inefficient index usage  
      if (query.efficiency.examineToReturnRatio > this.thresholds.examineToReturnRatio) {
        issues.inefficientIndexUsage.push(query);
      }

      // Large result sets
      if (query.docsReturned > 10000) {
        issues.largeResultSets.push(query);
      }

      // Long running queries
      if (query.duration > 1000) {
        issues.longRunningQueries.push(query);
      }
    });

    return {
      totalQueries: queries.length,
      issues: issues,
      recommendations: this.generatePerformanceRecommendations(issues)
    };
  }

  generatePerformanceRecommendations(issues) {
    const recommendations = [];

    if (issues.collectionScans.length > 0) {
      recommendations.push({
        priority: 'high',
        issue: 'Collection Scans Detected',
        message: `${issues.collectionScans.length} queries performing full collection scans`,
        solution: 'Create appropriate indexes for frequently queried fields'
      });
    }

    if (issues.inefficientIndexUsage.length > 0) {
      recommendations.push({
        priority: 'medium', 
        issue: 'Inefficient Index Usage',
        message: `${issues.inefficientIndexUsage.length} queries examining too many documents`,
        solution: 'Optimize compound indexes and query selectivity'
      });
    }

    if (issues.longRunningQueries.length > 0) {
      recommendations.push({
        priority: 'high',
        issue: 'Long Running Queries',
        message: `${issues.longRunningQueries.length} queries taking over 1 second`,
        solution: 'Review query patterns and add appropriate indexes'
      });
    }

    return recommendations;
  }
}

-- SQL-style resource monitoring concepts
SELECT 
  query_text,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 10;

-- Monitor index usage efficiency
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_tup_read,
  idx_tup_fetch,
  CASE WHEN idx_tup_read > 0 
    THEN round(100.0 * idx_tup_fetch / idx_tup_read, 2)
    ELSE 0 
  END AS fetch_ratio
FROM pg_stat_user_indexes
ORDER BY fetch_ratio DESC;

// MongoDB resource utilization monitoring
class ResourceMonitor {
  constructor(db) {
    this.db = db;
  }

  async getServerStatus() {
    const status = await this.db.admin().command({ serverStatus: 1 });

    return {
      connections: {
        current: status.connections.current,
        available: status.connections.available,
        totalCreated: status.connections.totalCreated
      },
      memory: {
        resident: status.mem.resident,
        virtual: status.mem.virtual,
        mapped: status.mem.mapped
      },
      opcounters: status.opcounters,
      wiredTiger: {
        cacheSize: status.wiredTiger?.cache?.['maximum bytes configured'],
        cachePressure: status.wiredTiger?.cache?.['percentage overhead']
      },
      locks: status.locks
    };
  }

  async getDatabaseStats(dbName) {
    const stats = await this.db.stats();

    return {
      collections: stats.collections,
      objects: stats.objects,
      avgObjSize: stats.avgObjSize,
      dataSize: stats.dataSize,
      storageSize: stats.storageSize,
      indexes: stats.indexes,
      indexSize: stats.indexSize,
      fileSize: stats.fileSize
    };
  }

  async getCollectionStats(collectionName) {
    const stats = await this.db.collection(collectionName).stats();

    return {
      size: stats.size,
      count: stats.count,
      avgObjSize: stats.avgObjSize,
      storageSize: stats.storageSize,
      totalIndexSize: stats.totalIndexSize,
      indexSizes: stats.indexSizes
    };
  }

  async generateResourceReport() {
    const serverStatus = await this.getServerStatus();
    const dbStats = await this.getDatabaseStats();

    return {
      timestamp: new Date(),
      server: serverStatus,
      database: dbStats,
      healthScore: this.calculateHealthScore(serverStatus, dbStats),
      alerts: this.generateResourceAlerts(serverStatus, dbStats)
    };
  }

  calculateHealthScore(serverStatus, dbStats) {
    let score = 100;

    // Connection utilization
    const connUtilization = serverStatus.connections.current / 
      serverStatus.connections.available;
    if (connUtilization > 0.8) score -= 20;
    else if (connUtilization > 0.6) score -= 10;

    // Memory utilization  
    if (serverStatus.memory.resident > 8000) score -= 15;

    // Cache efficiency (if available)
    if (serverStatus.wiredTiger?.cachePressure > 95) score -= 25;

    return Math.max(0, score);
  }
}

// Optimized connection configuration
const { MongoClient } = require('mongodb');

const optimizedClient = new MongoClient(connectionString, {
  // Connection pool settings
  maxPoolSize: 50,           // Maximum connections in pool
  minPoolSize: 5,            // Minimum connections to maintain
  maxIdleTimeMS: 30000,      // Close connections after 30s idle

  // Performance settings
  maxConnecting: 10,         // Maximum concurrent connection attempts
  connectTimeoutMS: 10000,   // Connection timeout
  socketTimeoutMS: 45000,    // Socket timeout
  serverSelectionTimeoutMS: 30000, // Server selection timeout

  // Monitoring and logging
  monitorCommands: true,     // Enable command monitoring
  loggerLevel: 'info',

  // Write concern optimization
  writeConcern: {
    w: 'majority',
    j: true,
    wtimeout: 10000
  },

  // Read preference for performance
  readPreference: 'primaryPreferred',
  readConcern: { level: 'majority' }
});

// Connection event monitoring
optimizedClient.on('connectionPoolCreated', (event) => {
  console.log('Connection pool created:', event);
});

optimizedClient.on('commandStarted', (event) => {
  if (event.durationMS > 100) {
    console.log('Slow command detected:', {
      command: event.commandName,
      duration: event.durationMS,
      collection: event.command?.collection
    });
  }
});

// Query result caching system
class QueryCache {
  constructor(ttlSeconds = 300) {
    this.cache = new Map();
    this.ttl = ttlSeconds * 1000;
  }

  generateCacheKey(collection, query, options) {
    return JSON.stringify({ collection, query, options });
  }

  async get(collection, query, options) {
    const key = this.generateCacheKey(collection, query, options);
    const cached = this.cache.get(key);

    if (cached && (Date.now() - cached.timestamp) < this.ttl) {
      return cached.result;
    }

    this.cache.delete(key);
    return null;
  }

  set(collection, query, options, result) {
    const key = this.generateCacheKey(collection, query, options);
    this.cache.set(key, {
      result: result,
      timestamp: Date.now()
    });
  }

  clear(collection) {
    for (const [key] of this.cache) {
      if (key.includes(`"collection":"${collection}"`)) {
        this.cache.delete(key);
      }
    }
  }
}

// Cached query execution
class CachedDatabase {
  constructor(db, cache) {
    this.db = db;
    this.cache = cache;
  }

  async find(collection, query, options = {}) {
    // Check cache first
    const cached = await this.cache.get(collection, query, options);
    if (cached) {
      return cached;
    }

    // Execute query
    const result = await this.db.collection(collection)
      .find(query, options).toArray();

    // Cache result if query is cacheable
    if (this.isCacheable(query, options)) {
      this.cache.set(collection, query, options, result);
    }

    return result;
  }

  isCacheable(query, options) {
    // Don't cache queries with current date references
    const queryStr = JSON.stringify(query);
    return !queryStr.includes('$now') && 
           !queryStr.includes('new Date') &&
           (!options.sort || Object.keys(options.sort).length <= 2);
  }
}

-- QueryLeaf automatically optimizes SQL-style queries
WITH daily_sales AS (
  SELECT 
    DATE(created_at) as sale_date,
    customer_id,
    SUM(total_amount) as daily_total,
    COUNT(*) as order_count
  FROM orders 
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    AND status = 'completed'
  GROUP BY DATE(created_at), customer_id
),
customer_metrics AS (
  SELECT 
    c.customer_id,
    c.name,
    c.region,
    ds.sale_date,
    ds.daily_total,
    ds.order_count,
    ROW_NUMBER() OVER (
      PARTITION BY c.customer_id 
      ORDER BY ds.daily_total DESC
    ) as purchase_rank
  FROM daily_sales ds
  JOIN customers c ON ds.customer_id = c.customer_id
)
SELECT 
  region,
  COUNT(DISTINCT customer_id) as active_customers,
  SUM(daily_total) as total_revenue,
  AVG(daily_total) as avg_daily_revenue,
  MAX(daily_total) as highest_daily_purchase
FROM customer_metrics
WHERE purchase_rank <= 5  -- Top 5 purchase days per customer
GROUP BY region
ORDER BY total_revenue DESC;

-- QueryLeaf automatically:
-- 1. Creates optimal compound indexes
-- 2. Chooses efficient aggregation pipeline stages
-- 3. Uses index intersection when beneficial
-- 4. Provides query performance insights
-- 5. Suggests index optimizations
-- 6. Monitors query execution statistics