Aller au contenu principal

Database Query Optimization Guide

Overview​

This optimization eliminates unnecessary database queries when generating thumbnail URLs by implementing:

  1. Website configuration caching - Singleton data cached at startup
  2. Module slug denormalization - Store module_slug directly on items

Performance Impact​

Before:

  • 2 database queries per thumbnail generation (websiteManagement + module)
  • Sequential execution (~100-200ms total)

After:

  • 0 database queries in normal operation (cached + denormalized)
  • Fallback: 1 query only if module_slug missing (~50ms)
  • ~100-200ms faster per request

Implementation Details​

1. Website Config Cache (src/utils/websiteConfig.util.ts)​

Caches the website base URL at application startup:

import { getWebsiteBaseUrl, buildThumbnailUrl } from "../utils/websiteConfig.util";

// Get cached base URL
const baseUrl = getWebsiteBaseUrl();

// Build thumbnail URL
const url = buildThumbnailUrl(moduleSlug, thumbnailSlug);

Initialization: Automatically called in src/index.ts after DB connection

Cache refresh: Automatically refreshed when website settings are updated via websiteManagement.controller.ts

2. Module Slug Denormalization​

Added module_slug field to:

  • ItemDocument interface (base.controller.ts)
  • INews interface (interfaces/news.interface.ts)
  • News model schema (models/news.model.ts)

Auto-population:

  • New items: module_slug set during creation
  • Updated items: Fallback query if missing, then stored for future use

3. Migration for Existing Data​

Run the migration script to populate module_slug for existing records:

npx ts-node src/scripts/migrateModuleSlug.ts

Important: Run this once after deploying the new code.

Adding module_slug to Other Models​

If you have other models with module_id (e.g., Product, Event), add the field:

  1. Update the interface:
export interface IProduct extends Document {
  module_id: Types.ObjectId;
  module_slug?: string;  // Add this
  // ... other fields
}
  1. Update the schema:
const ProductSchema = new Schema<IProduct>({
  module_id: {
    type: Schema.Types.ObjectId,
    required: true,
  },
  module_slug: {  // Add this
    type: String,
    required: false,
  },
  // ... other fields
});
  1. Update the migration script (src/scripts/migrateModuleSlug.ts) to include the new model.

Monitoring​

The cache initialization logs to console on startup:

[WebsiteConfig] Initialized with base URL: example.com

If initialization fails, it falls back to localhost and logs a warning.

Troubleshooting​

Error: "Website configuration not initialized"

  • Ensure initializeWebsiteConfig() is called in src/index.ts after DB connection
  • Check that websiteManagement collection has a document with singleton: "singleton"

Thumbnail URLs showing wrong domain

  • Cache may be stale - restart the server or update website settings to trigger refresh
  • Manually refresh: Call refreshWebsiteConfig() from websiteConfig.util

Missing module_slug on items

  • Run the migration script for existing data
  • New items should auto-populate the field
  • Fallback query will populate it on next update