MongoDB Search and Full-Text Indexing: Advanced Query Optimization Strategies for Production Applications

-- Inefficient traditional search approaches that don't scale

-- Problem 1: LIKE patterns with leading wildcards (full table scan)
SELECT 
    product_id,
    product_name,
    description,
    category,
    price
FROM products
WHERE product_name ILIKE '%wireless%'
   OR description ILIKE '%bluetooth%'
   OR category ILIKE '%audio%'
ORDER BY product_name;

-- Problems with this approach:
-- 1. No index utilization - requires full collection scan
-- 2. Case-insensitive LIKE operations are expensive
-- 3. No relevance scoring or ranking
-- 4. Poor performance with large datasets (>100K documents)
-- 5. No language-specific search capabilities
-- 6. Cannot handle synonyms, stemming, or fuzzy matching
-- 7. Complex multi-field searches become increasingly expensive

-- Problem 2: Multiple field searches with OR conditions (inefficient)
SELECT 
    blog_id,
    title,
    content,
    author,
    tags,
    published_date,
    view_count
FROM blog_posts
WHERE (title ILIKE '%machine learning%' OR title ILIKE '%AI%' OR title ILIKE '%artificial intelligence%')
   OR (content ILIKE '%neural network%' OR content ILIKE '%deep learning%')
   OR (tags::text ILIKE '%data science%')
   OR (author ILIKE '%expert%')
ORDER BY published_date DESC, view_count DESC;

-- Problems:
-- 1. Multiple OR conditions prevent index optimization
-- 2. No relevance scoring - results ordered by publication date, not relevance
-- 3. Searches across different field types (text, arrays) are complex
-- 4. Cannot boost importance of matches in title vs content
-- 5. No support for partial word matches or typo tolerance
-- 6. Performance degrades exponentially with dataset size

-- Problem 3: Complex e-commerce search with filters (unoptimized)
SELECT 
    p.product_id,
    p.product_name,
    p.description,
    p.category,
    p.brand,
    p.price,
    p.rating,
    p.review_count,
    CASE 
        WHEN p.product_name ILIKE '%search_term%' THEN 3
        WHEN p.description ILIKE '%search_term%' THEN 2
        WHEN p.category ILIKE '%search_term%' THEN 1
        ELSE 0
    END as relevance_score
FROM products p
JOIN product_inventory pi ON p.product_id = pi.product_id
WHERE (p.product_name ILIKE '%wireless headphones%' 
       OR p.description ILIKE '%wireless headphones%' 
       OR p.category ILIKE '%headphones%')
  AND pi.quantity_available > 0
  AND p.price BETWEEN 50 AND 300
  AND p.rating >= 4.0
  AND p.brand IN ('Sony', 'Bose', 'Apple', 'Samsung')
ORDER BY relevance_score DESC, p.rating DESC, p.review_count DESC;

-- Problems:
-- 1. Manual relevance scoring is simplistic and doesn't handle phrase matching
-- 2. CASE statement for scoring prevents index usage
-- 3. Multiple ILIKE operations across large text fields are expensive
-- 4. Cannot handle variations in search terms (e.g., "headphone" vs "headphones")
-- 5. No support for fuzzy matching or typo tolerance
-- 6. Filter conditions after search prevent search optimization
-- 7. Results ranking doesn't consider text search relevance properly

-- Problem 4: Multi-language content search (inadequate)
SELECT 
    document_id,
    title_english,
    title_spanish,
    title_french,
    content_english,
    content_spanish,
    content_french,
    language,
    created_date
FROM multilingual_documents
WHERE (language = 'en' AND (title_english ILIKE '%innovation%' OR content_english ILIKE '%technology%'))
   OR (language = 'es' AND (title_spanish ILIKE '%innovación%' OR content_spanish ILIKE '%tecnología%'))
   OR (language = 'fr' AND (title_french ILIKE '%innovation%' OR content_french ILIKE '%technologie%'))
ORDER BY created_date DESC;

-- Problems:
-- 1. Requires maintaining separate fields for each language
-- 2. No language-specific stemming or analysis
-- 3. Search terms must be manually translated
-- 4. Complex query structure for multiple languages
-- 5. Cannot handle mixed-language content
-- 6. No support for language-specific stop words or stemming
-- 7. Difficult to maintain and extend to new languages

-- These traditional approaches face fundamental limitations:
-- 1. Performance Issues: Full table scans, no search-optimized indexes
-- 2. Relevance Problems: No intelligent ranking or scoring
-- 3. Language Barriers: Limited multi-language and stemming support
-- 4. Maintenance Complexity: Complex query structures that are hard to optimize
-- 5. Scalability Limitations: Performance degrades significantly with data growth
-- 6. User Experience: Poor search quality and slow response times
-- 7. Development Overhead: Manual implementation of search features

// MongoDB Advanced Full-Text Search Implementation

// Create comprehensive text index with field weighting
db.products.createIndex({
  product_name: "text",
  description: "text", 
  category: "text",
  tags: "text",
  specifications: "text"
}, {
  weights: {
    product_name: 10,    // Highest priority - exact name matches
    category: 8,         // High priority - category relevance
    tags: 6,            // Medium-high priority - structured metadata
    description: 4,      // Medium priority - detailed descriptions
    specifications: 2    // Lower priority - technical details
  },
  name: "product_search_index",
  default_language: "english",
  language_override: "search_language"
})

// Advanced search with scoring and filtering
db.products.aggregate([
  // Stage 1: Text search with advanced matching
  {
    $match: {
      $and: [
        {
          $text: {
            $search: "wireless bluetooth headphones",
            $caseSensitive: false,
            $diacriticSensitive: false
          }
        },
        {
          price: { $gte: 50, $lte: 300 }
        },
        {
          "inventory.quantity_available": { $gt: 0 }
        },
        {
          rating: { $gte: 4.0 }
        },
        {
          brand: { $in: ["Sony", "Bose", "Apple", "Samsung"] }
        }
      ]
    }
  },

  // Stage 2: Add comprehensive search scoring
  {
    $addFields: {
      // MongoDB's built-in text relevance score
      text_score: { $meta: "textScore" },

      // Business logic scoring
      business_score: {
        $add: [
          { $multiply: ["$rating", 2] },           // Rating boost
          { $divide: [{ $ln: "$review_count" }, 10] }, // Review count boost (logarithmic)
          { $cond: [{ $eq: ["$featured", true] }, 5, 0] }, // Featured product boost
          { $cond: [{ $gt: ["$inventory.quantity_available", 10] }, 2, 0] } // High inventory boost
        ]
      },

      // Combined relevance score
      combined_score: {
        $add: [
          { $multiply: [{ $meta: "textScore" }, 3] }, // Text relevance (3x weight)
          "$business_score"                           // Business scoring
        ]
      },

      // Add search result metadata
      search_metadata: {
        matched_fields: {
          $switch: {
            branches: [
              { case: { $regexMatch: { input: "$product_name", regex: /wireless|bluetooth|headphones/i } }, then: ["product_name"] },
              { case: { $regexMatch: { input: "$description", regex: /wireless|bluetooth|headphones/i } }, then: ["description"] },
              { case: { $regexMatch: { input: "$category", regex: /wireless|bluetooth|headphones/i } }, then: ["category"] }
            ],
            default: ["description"]
          }
        },
        search_terms_found: {
          $size: {
            $filter: {
              input: ["wireless", "bluetooth", "headphones"],
              cond: {
                $or: [
                  { $regexMatch: { input: "$product_name", regex: { $concat: [".*", "$$this", ".*"] }, options: "i" } },
                  { $regexMatch: { input: "$description", regex: { $concat: [".*", "$$this", ".*"] }, options: "i" } }
                ]
              }
            }
          }
        }
      }
    }
  },

  // Stage 3: Sort by combined relevance and business metrics
  {
    $sort: {
      combined_score: -1,
      rating: -1,
      review_count: -1
    }
  },

  // Stage 4: Add search result highlighting (simulated)
  {
    $addFields: {
      highlighted_name: {
        $replaceAll: {
          input: "$product_name",
          find: { $regex: "(wireless|bluetooth|headphones)", $options: "i" },
          replacement: "<mark>$1</mark>"
        }
      },
      highlighted_description: {
        $substr: [
          {
            $replaceAll: {
              input: "$description",
              find: { $regex: "(wireless|bluetooth|headphones)", $options: "i" },
              replacement: "<mark>$1</mark>"
            }
          },
          0, 200
        ]
      }
    }
  },

  // Stage 5: Project final search results
  {
    $project: {
      product_id: 1,
      product_name: 1,
      highlighted_name: 1,
      description: 1,
      highlighted_description: 1,
      category: 1,
      brand: 1,
      price: 1,
      rating: 1,
      review_count: 1,
      text_score: 1,
      business_score: 1,
      combined_score: 1,
      search_metadata: 1,
      "inventory.quantity_available": 1,
      featured: 1
    }
  },

  // Stage 6: Limit results for pagination
  { $limit: 20 }
])

// E-commerce product search with sophisticated field weighting
db.products.createIndex({
  // Primary product information (highest weights)
  product_name: "text",
  brand: "text",
  model: "text",

  // Product categorization (high weights)
  category: "text",
  subcategory: "text",
  tags: "text",

  // Descriptive content (medium weights)
  short_description: "text",
  long_description: "text",
  key_features: "text",

  // Technical specifications (lower weights)
  specifications: "text",
  technical_details: "text",

  // User-generated content (contextual weights)
  "reviews.title": "text",
  "reviews.content": "text"
}, {
  weights: {
    // Product identity - highest priority
    product_name: 15,
    brand: 12,
    model: 10,

    // Categorization - high priority
    category: 9,
    subcategory: 8,
    tags: 7,

    // Marketing content - medium-high priority
    short_description: 6,
    key_features: 5,
    long_description: 4,

    // Technical content - medium priority
    specifications: 3,
    technical_details: 2,

    // User content - lower priority but valuable for discovery
    "reviews.title": 3,
    "reviews.content": 1
  },
  name: "comprehensive_product_search",
  default_language: "english",
  language_override: "product_language",
  textIndexVersion: 3 // Latest version for better performance
})

// Blog/Content search index with content-specific weighting
db.blog_posts.createIndex({
  title: "text",
  subtitle: "text",
  content: "text",
  summary: "text",
  tags: "text",
  category: "text",
  "author.name": "text",
  "author.bio": "text"
}, {
  weights: {
    title: 20,        // Titles are most important for relevance
    subtitle: 15,     // Secondary headlines
    summary: 10,      // Executive summaries
    tags: 8,         // Structured metadata
    category: 6,     // Topic categorization
    "author.name": 5, // Author attribution
    content: 3,      // Full content (lower weight due to length)
    "author.bio": 1  // Background information
  },
  name: "blog_content_search",
  default_language: "english"
})

// Multi-language document search with language-specific optimization
db.documents.createIndex({
  "title.english": "text",
  "title.spanish": "text", 
  "title.french": "text",
  "content.english": "text",
  "content.spanish": "text",
  "content.french": "text",
  keywords: "text",
  global_tags: "text"
}, {
  weights: {
    "title.english": 10,
    "title.spanish": 10,
    "title.french": 10,
    "content.english": 5,
    "content.spanish": 5,
    "content.french": 5,
    keywords: 8,
    global_tags: 6
  },
  name: "multilingual_document_search",
  language_override: "primary_language"
})

// Language-specific search implementation
async function performLanguageAwareSearch(searchTerms, targetLanguage = 'english', options = {}) {
  const languageConfigs = {
    'english': {
      stemming: true,
      stopWords: ['the', 'a', 'an', 'and', 'or', 'but', 'in', 'on', 'at', 'to', 'for', 'of', 'with', 'by'],
      synonyms: {
        'car': ['automobile', 'vehicle', 'auto'],
        'phone': ['mobile', 'smartphone', 'cell'],
        'computer': ['pc', 'laptop', 'desktop', 'workstation']
      }
    },
    'spanish': {
      stemming: true,
      stopWords: ['el', 'la', 'de', 'que', 'y', 'a', 'en', 'un', 'es', 'se', 'no', 'te', 'lo', 'le'],
      synonyms: {
        'coche': ['automóvil', 'vehículo', 'auto'],
        'teléfono': ['móvil', 'smartphone', 'celular'],
        'computadora': ['ordenador', 'pc', 'portátil']
      }
    },
    'french': {
      stemming: true,
      stopWords: ['le', 'de', 'et', 'à', 'un', 'il', 'être', 'et', 'en', 'avoir', 'que', 'pour'],
      synonyms: {
        'voiture': ['automobile', 'véhicule', 'auto'],
        'téléphone': ['mobile', 'smartphone', 'portable'],
        'ordinateur': ['pc', 'portable', 'laptop']
      }
    }
  };

  const config = languageConfigs[targetLanguage] || languageConfigs['english'];

  // Expand search terms with synonyms
  let expandedTerms = searchTerms;
  for (const [original, synonyms] of Object.entries(config.synonyms)) {
    if (searchTerms.toLowerCase().includes(original)) {
      expandedTerms += ' ' + synonyms.join(' ');
    }
  }

  // Build language-aware search query
  const searchQuery = {
    $text: {
      $search: expandedTerms,
      $language: targetLanguage,
      $caseSensitive: false,
      $diacriticSensitive: false
    }
  };

  // Add additional filters if provided
  if (options.filters) {
    Object.assign(searchQuery, options.filters);
  }

  return await db.multilingual_content.aggregate([
    { $match: searchQuery },
    {
      $addFields: {
        relevance_score: { $meta: "textScore" },
        language_match_boost: {
          $cond: [
            { $eq: ["$primary_language", targetLanguage] },
            2.0,  // Boost documents in target language
            1.0
          ]
        },
        final_score: {
          $multiply: [{ $meta: "textScore" }, "$language_match_boost"]
        }
      }
    },
    { $sort: { final_score: -1, created_at: -1 } },
    { $limit: options.limit || 20 }
  ]).toArray();
}

// Usage examples for different languages
const englishResults = await performLanguageAwareSearch(
  "machine learning artificial intelligence", 
  "english",
  { filters: { category: "technology" }, limit: 15 }
);

const spanishResults = await performLanguageAwareSearch(
  "aprendizaje automático inteligencia artificial",
  "spanish", 
  { filters: { category: "tecnología" }, limit: 15 }
);

const frenchResults = await performLanguageAwareSearch(
  "apprentissage automatique intelligence artificielle",
  "french",
  { filters: { category: "technologie" }, limit: 15 }
);

// Advanced search result ranking with multiple scoring factors
db.products.aggregate([
  // Stage 1: Initial text search
  {
    $match: {
      $text: {
        $search: "premium wireless noise cancelling headphones",
        $caseSensitive: false
      }
    }
  },

  // Stage 2: Comprehensive scoring algorithm
  {
    $addFields: {
      // Base text relevance score
      text_relevance: { $meta: "textScore" },

      // Popularity scoring (normalized)
      popularity_score: {
        $add: [
          // Review count influence (logarithmic to prevent dominance)
          { $multiply: [{ $ln: { $add: ["$review_count", 1] } }, 0.1] },
          // Rating influence (4.0+ ratings get boost)
          { $cond: [{ $gte: ["$average_rating", 4.0] }, { $multiply: ["$average_rating", 0.5] }, 0] },
          // Sales velocity (recent sales boost)
          { $multiply: [{ $ifNull: ["$sales_last_30_days", 0] }, 0.001] }
        ]
      },

      // Business priority scoring
      business_priority_score: {
        $add: [
          // Featured product boost
          { $cond: [{ $eq: ["$featured_product", true] }, 3.0, 0] },
          // New product launch boost (within 90 days)
          { 
            $cond: [
              { $gte: ["$launch_date", { $subtract: [new Date(), 90 * 24 * 60 * 60 * 1000] }] },
              2.0,
              0
            ]
          },
          // High margin product boost
          { $cond: [{ $gte: ["$profit_margin", 0.4] }, 1.5, 0] },
          // Brand partnership boost
          { $cond: [{ $in: ["$brand", ["Apple", "Sony", "Bose"]] }, 1.0, 0] }
        ]
      },

      // Availability and inventory scoring
      inventory_score: {
        $switch: {
          branches: [
            { case: { $gt: ["$inventory_quantity", 100] }, then: 2.0 },     // High stock
            { case: { $gt: ["$inventory_quantity", 50] }, then: 1.5 },      // Medium stock
            { case: { $gt: ["$inventory_quantity", 10] }, then: 1.0 },      // Low stock
            { case: { $gt: ["$inventory_quantity", 0] }, then: 0.5 }        // Very low stock
          ],
          default: 0 // Out of stock
        }
      },

      // Price competitiveness scoring
      price_competitiveness_score: {
        $cond: [
          { $and: [{ $gte: ["$price", "$category_price_min"] }, { $lte: ["$price", "$category_price_max"] }] },
          {
            $subtract: [
              2.0,
              { $divide: [{ $subtract: ["$price", "$category_price_min"] }, { $subtract: ["$category_price_max", "$category_price_min"] }] }
            ]
          },
          0
        ]
      },

      // Search term match quality scoring
      search_quality_score: {
        $add: [
          // Exact phrase match bonus
          { $cond: [{ $regexMatch: { input: { $toLower: "$product_name" }, regex: "premium wireless noise cancelling headphones" } }, 5.0, 0] },
          // Individual term matches in title
          { $cond: [{ $regexMatch: { input: { $toLower: "$product_name" }, regex: "premium" } }, 1.0, 0] },
          { $cond: [{ $regexMatch: { input: { $toLower: "$product_name" }, regex: "wireless" } }, 1.0, 0] },
          { $cond: [{ $regexMatch: { input: { $toLower: "$product_name" }, regex: "noise cancelling" } }, 2.0, 0] },
          { $cond: [{ $regexMatch: { input: { $toLower: "$product_name" }, regex: "headphones" } }, 1.0, 0] }
        ]
      },

      // User behavior scoring (if available)
      user_behavior_score: {
        $add: [
          // Click-through rate boost
          { $multiply: [{ $ifNull: ["$search_ctr", 0] }, 3.0] },
          // Conversion rate boost
          { $multiply: [{ $ifNull: ["$conversion_rate", 0] }, 5.0] },
          // View-to-purchase rate
          { $multiply: [{ $ifNull: ["$view_to_purchase_rate", 0] }, 4.0] }
        ]
      },

      // Calculate final composite score
      final_search_score: {
        $add: [
          { $multiply: ["$text_relevance", 4.0] },           // Text relevance (40% weight)
          { $multiply: ["$popularity_score", 2.0] },         // Popularity (20% weight)
          { $multiply: ["$business_priority_score", 1.5] },  // Business priority (15% weight)
          { $multiply: ["$inventory_score", 1.0] },          // Inventory (10% weight)
          { $multiply: ["$price_competitiveness_score", 0.75] }, // Price (7.5% weight)
          { $multiply: ["$search_quality_score", 0.5] },     // Search quality (5% weight)
          { $multiply: ["$user_behavior_score", 0.25] }      // User behavior (2.5% weight)
        ]
      }
    }
  },

  // Stage 3: Sort by final score and apply business rules
  {
    $sort: {
      final_search_score: -1,
      inventory_quantity: -1,  // Secondary sort for tied scores
      average_rating: -1       // Tertiary sort
    }
  },

  // Stage 4: Add search result metadata for analytics
  {
    $addFields: {
      search_result_metadata: {
        query_timestamp: new Date(),
        scoring_breakdown: {
          text_relevance: "$text_relevance",
          popularity_score: "$popularity_score",
          business_priority_score: "$business_priority_score",
          inventory_score: "$inventory_score",
          price_competitiveness_score: "$price_competitiveness_score",
          search_quality_score: "$search_quality_score",
          user_behavior_score: "$user_behavior_score",
          final_score: "$final_search_score"
        },
        result_position: { $add: [{ $indexOfArray: [{ $map: { input: "$$ROOT", as: "doc", in: "$$doc._id" } }, "$_id"] }, 1] }
      }
    }
  },

  // Stage 5: Project final search results
  {
    $project: {
      product_id: 1,
      product_name: 1,
      brand: 1,
      model: 1,
      price: 1,
      average_rating: 1,
      review_count: 1,
      inventory_quantity: 1,
      product_images: { $slice: ["$images", 3] }, // Limit images for performance
      key_features: { $slice: ["$features", 5] }, // Top 5 features
      final_search_score: 1,
      search_result_metadata: 1,

      // Add highlighted content for search results display
      highlighted_content: {
        title_highlight: {
          $replaceAll: {
            input: "$product_name",
            find: { $regex: "(premium|wireless|noise cancelling|headphones)", $options: "i" },
            replacement: "<mark>$1</mark>"
          }
        },
        description_snippet: {
          $substr: ["$short_description", 0, 150]
        }
      }
    }
  },

  { $limit: 20 }
])

// Personalized search implementation
async function performPersonalizedSearch(userId, searchQuery, options = {}) {
  // Get user profile and search history
  const userProfile = await db.user_profiles.findOne({ user_id: userId });
  const searchHistory = await db.search_history.find({ 
    user_id: userId 
  }).sort({ timestamp: -1 }).limit(100).toArray();

  // Extract user preferences from history
  const userPreferences = {
    preferred_brands: extractPreferredBrands(searchHistory),
    preferred_categories: extractPreferredCategories(searchHistory),
    price_range_preference: calculatePriceRangePreference(searchHistory),
    feature_preferences: extractFeaturePreferences(searchHistory),
    search_patterns: analyzeSearchPatterns(searchHistory)
  };

  return await db.products.aggregate([
    // Stage 1: Text search
    {
      $match: {
        $text: {
          $search: searchQuery,
          $caseSensitive: false
        }
      }
    },

    // Stage 2: Add personalization scoring
    {
      $addFields: {
        base_text_score: { $meta: "textScore" },

        // Personalization factors
        personalization_score: {
          $add: [
            // Brand preference boost
            {
              $cond: [
                { $in: ["$brand", userPreferences.preferred_brands] },
                2.0,
                0
              ]
            },
            // Category preference boost
            {
              $cond: [
                { $in: ["$category", userPreferences.preferred_categories] },
                1.5,
                0
              ]
            },
            // Price range compatibility
            {
              $cond: [
                {
                  $and: [
                    { $gte: ["$price", userPreferences.price_range_preference.min] },
                    { $lte: ["$price", userPreferences.price_range_preference.max] }
                  ]
                },
                1.0,
                -0.5
              ]
            },
            // Feature preference alignment
            {
              $size: {
                $setIntersection: [
                  "$key_features",
                  userPreferences.feature_preferences
                ]
              }
            }
          ]
        },

        // Demographic targeting (if applicable)
        demographic_score: {
          $add: [
            // Age group targeting
            {
              $cond: [
                { $in: [userProfile.age_group, "$target_demographics.age_groups"] },
                0.5,
                0
              ]
            },
            // Interest targeting
            {
              $multiply: [
                {
                  $size: {
                    $setIntersection: [
                      userProfile.interests,
                      "$target_demographics.interests"
                    ]
                  }
                },
                0.1
              ]
            }
          ]
        },

        // Calculate personalized final score
        personalized_final_score: {
          $add: [
            { $multiply: [{ $meta: "textScore" }, 3.0] },
            { $multiply: ["$personalization_score", 2.0] },
            "$demographic_score"
          ]
        }
      }
    },

    // Stage 3: Sort by personalized score
    {
      $sort: {
        personalized_final_score: -1,
        average_rating: -1,
        review_count: -1
      }
    },

    // Stage 4: Log search event for future personalization
    {
      $addFields: {
        search_event: {
          user_id: userId,
          search_query: searchQuery,
          timestamp: new Date(),
          personalization_applied: true
        }
      }
    },

    { $limit: options.limit || 20 }
  ]).toArray();
}

// Helper functions for personalization
function extractPreferredBrands(searchHistory) {
  const brandCounts = {};
  searchHistory.forEach(search => {
    if (search.clicked_products) {
      search.clicked_products.forEach(product => {
        brandCounts[product.brand] = (brandCounts[product.brand] || 0) + 1;
      });
    }
  });
  return Object.keys(brandCounts)
    .sort((a, b) => brandCounts[b] - brandCounts[a])
    .slice(0, 5);
}

function calculatePriceRangePreference(searchHistory) {
  const prices = [];
  searchHistory.forEach(search => {
    if (search.purchased_products) {
      search.purchased_products.forEach(product => {
        prices.push(product.price);
      });
    }
  });

  if (prices.length === 0) return { min: 0, max: 1000 };

  prices.sort((a, b) => a - b);
  return {
    min: Math.max(0, prices[Math.floor(prices.length * 0.25)] * 0.8),
    max: prices[Math.floor(prices.length * 0.75)] * 1.2
  };
}

// Performance-optimized index strategies for different search scenarios

// Strategy 1: High-frequency, simple searches (e-commerce product search)
// Optimized for speed over comprehensive coverage
db.products_fast_search.createIndex({
  product_name: "text",
  brand: "text",
  category: "text"
}, {
  weights: {
    product_name: 10,
    brand: 8,
    category: 5
  },
  name: "fast_product_search",
  sparse: true,  // Only index documents with text fields
  background: true,
  textIndexVersion: 3
});

// Strategy 2: Comprehensive content search (documentation, blogs)
// Optimized for relevance over speed
db.content_comprehensive_search.createIndex({
  title: "text",
  content: "text",
  tags: "text",
  category: "text",
  author: "text",
  "metadata.keywords": "text"
}, {
  weights: {
    title: 15,
    "metadata.keywords": 10,
    tags: 8,
    category: 6,
    author: 4,
    content: 3
  },
  name: "comprehensive_content_search",
  default_language: "english",
  language_override: "content_language",
  textIndexVersion: 3
});

// Strategy 3: Multi-language optimized search
// Separate indexes per language for optimal performance
const languages = ['english', 'spanish', 'french', 'german', 'italian'];

languages.forEach(lang => {
  db.multilingual_content.createIndex({
    [`title.${lang}`]: "text",
    [`content.${lang}`]: "text",
    [`summary.${lang}`]: "text",
    global_tags: "text"
  }, {
    weights: {
      [`title.${lang}`]: 12,
      [`summary.${lang}`]: 8,
      [`content.${lang}`]: 5,
      global_tags: 6
    },
    name: `search_${lang}`,
    default_language: lang,
    partialFilterExpression: { primary_language: lang },
    background: true
  });
});

// Strategy 4: Compound indexes for filtered searches
// Combine text search with common filter conditions
db.products_filtered_search.createIndex({
  category: 1,
  price: 1,
  availability_status: 1,
  product_name: "text",
  description: "text"
}, {
  weights: {
    product_name: 10,
    description: 5
  },
  name: "filtered_product_search"
});

// Performance monitoring for search indexes
async function analyzeSearchPerformance() {
  // Get index statistics
  const indexStats = await db.products.aggregate([
    { $indexStats: {} },
    {
      $match: {
        name: { $regex: /.*search.*/ } // Focus on search indexes
      }
    },
    {
      $project: {
        name: 1,
        accesses: "$accesses.ops",
        since: "$accesses.since"
      }
    }
  ]).toArray();

  console.log("Search Index Performance:", indexStats);

  // Analyze slow search queries
  const slowQueries = await db.system.profile.find({
    "command.aggregate": { $exists: true },
    "command.pipeline.0.$match.$text": { $exists: true },
    millis: { $gt: 100 } // Queries taking longer than 100ms
  }).sort({ ts: -1 }).limit(10).toArray();

  console.log("Slow Search Queries:", slowQueries);

  return { indexStats, slowQueries };
}

// Advanced search result caching implementation
class SearchResultCache {
  constructor(cacheConfig = {}) {
    this.cacheConfig = {
      defaultTTL: cacheConfig.defaultTTL || 300, // 5 minutes
      maxCacheSize: cacheConfig.maxCacheSize || 10000,
      popularQueryTTL: cacheConfig.popularQueryTTL || 900, // 15 minutes for popular queries
      personalizedTTL: cacheConfig.personalizedTTL || 60, // 1 minute for personalized results
      ...cacheConfig
    };
    this.cache = new Map();
    this.queryFrequency = new Map();
    this.cacheStats = {
      hits: 0,
      misses: 0,
      evictions: 0
    };
  }

  // Generate cache key considering all search factors
  generateCacheKey(searchParams) {
    const {
      query,
      filters,
      sort,
      limit,
      userId,
      language = 'english',
      personalized = false
    } = searchParams;

    const keyComponents = [
      `q:${query}`,
      `f:${JSON.stringify(filters || {})}`,
      `s:${JSON.stringify(sort || {})}`,
      `l:${limit || 20}`,
      `lang:${language}`
    ];

    if (personalized && userId) {
      keyComponents.push(`u:${userId}`);
    }

    return keyComponents.join('|');
  }

  // Determine appropriate TTL based on query characteristics
  calculateTTL(searchParams, queryFrequency = 0) {
    const { personalized, filters } = searchParams;

    // Personalized searches have shorter TTL
    if (personalized) {
      return this.cacheConfig.personalizedTTL;
    }

    // Popular queries get longer TTL
    if (queryFrequency > 10) {
      return this.cacheConfig.popularQueryTTL;
    }

    // Filtered searches (more specific) get longer TTL
    if (filters && Object.keys(filters).length > 0) {
      return this.cacheConfig.defaultTTL * 1.5;
    }

    return this.cacheConfig.defaultTTL;
  }

  // Get cached search results
  async get(searchParams) {
    const cacheKey = this.generateCacheKey(searchParams);
    const cached = this.cache.get(cacheKey);

    if (cached && cached.expiresAt > Date.now()) {
      this.cacheStats.hits++;

      // Update query frequency for cache optimization
      const currentFreq = this.queryFrequency.get(cacheKey) || 0;
      this.queryFrequency.set(cacheKey, currentFreq + 1);

      return {
        results: cached.data,
        cached: true,
        cacheAge: Date.now() - cached.createdAt
      };
    }

    this.cacheStats.misses++;
    return null;
  }

  // Store search results in cache
  async set(searchParams, results) {
    const cacheKey = this.generateCacheKey(searchParams);
    const queryFreq = this.queryFrequency.get(cacheKey) || 0;
    const ttl = this.calculateTTL(searchParams, queryFreq);

    // Cache size management
    if (this.cache.size >= this.cacheConfig.maxCacheSize) {
      this.evictLeastUsed();
    }

    const cacheEntry = {
      data: results,
      createdAt: Date.now(),
      expiresAt: Date.now() + (ttl * 1000),
      accessCount: 1,
      lastAccessed: Date.now(),
      queryFrequency: queryFreq
    };

    this.cache.set(cacheKey, cacheEntry);
    this.queryFrequency.set(cacheKey, queryFreq + 1);
  }

  // Evict least recently used entries when cache is full
  evictLeastUsed() {
    let oldestKey = null;
    let oldestAccess = Date.now();

    for (const [key, entry] of this.cache.entries()) {
      if (entry.lastAccessed < oldestAccess) {
        oldestAccess = entry.lastAccessed;
        oldestKey = key;
      }
    }

    if (oldestKey) {
      this.cache.delete(oldestKey);
      this.cacheStats.evictions++;
    }
  }

  // Invalidate cache entries when data changes
  async invalidatePattern(pattern) {
    const keysToDelete = [];

    for (const key of this.cache.keys()) {
      if (key.includes(pattern)) {
        keysToDelete.push(key);
      }
    }

    keysToDelete.forEach(key => this.cache.delete(key));
    return keysToDelete.length;
  }

  // Get cache performance statistics
  getStats() {
    const hitRate = this.cacheStats.hits / (this.cacheStats.hits + this.cacheStats.misses);

    return {
      ...this.cacheStats,
      hitRate: hitRate || 0,
      cacheSize: this.cache.size,
      popularQueries: Array.from(this.queryFrequency.entries())
        .sort((a, b) => b[1] - a[1])
        .slice(0, 10)
    };
  }
}

// Cached search implementation
const searchCache = new SearchResultCache({
  defaultTTL: 300,
  maxCacheSize: 5000,
  popularQueryTTL: 900,
  personalizedTTL: 60
});

async function performCachedSearch(searchParams) {
  const startTime = Date.now();

  // Try to get from cache first
  const cachedResult = await searchCache.get(searchParams);
  if (cachedResult) {
    return {
      ...cachedResult,
      searchTime: Date.now() - startTime,
      fromCache: true
    };
  }

  // Perform actual search
  const searchResults = await executeMongoSearch(searchParams);

  // Cache the results for future use
  await searchCache.set(searchParams, searchResults);

  return {
    results: searchResults,
    cached: false,
    searchTime: Date.now() - startTime,
    fromCache: false
  };
}

async function executeMongoSearch(searchParams) {
  const { query, filters = {}, sort = {}, limit = 20, userId, personalized = false } = searchParams;

  let pipeline = [
    {
      $match: {
        $and: [
          {
            $text: {
              $search: query,
              $caseSensitive: false,
              $diacriticSensitive: false
            }
          },
          filters
        ]
      }
    },
    {
      $addFields: {
        relevance_score: { $meta: "textScore" }
      }
    }
  ];

  // Add personalization if requested
  if (personalized && userId) {
    pipeline = await addPersonalizationStages(pipeline, userId);
  }

  // Add sorting and limiting
  pipeline.push(
    { $sort: { relevance_score: -1, ...sort } },
    { $limit: limit }
  );

  return await db.searchable_content.aggregate(pipeline).toArray();
}