Query Optimization Guide

This guide covers database query optimization patterns for Elite Events.

Overview

Efficient database queries are critical for performance. This guide covers:

• Select field optimization
• Avoiding N+1 queries
• Using DataLoaders
• Query caching strategies
• Index optimization

Select Field Optimization

The Problem

Fetching all fields when you only need a few wastes bandwidth and memory:

// Bad: Fetches all 20+ fields
const products = await prisma.product.findMany();

// Good: Fetches only what's needed
const products = await prisma.product.findMany({
  select: {
    id: true,
    title: true,
    price: true,
    slug: true,
  },
});

Using Standardized Selects

Import from @/lib/database/selects:

import { productListSelect, productDetailSelect } from '@/lib/database/selects';

// For product listings (minimal fields)
const products = await prisma.product.findMany({
  select: productListSelect,
});

// For product detail page (all fields)
const product = await prisma.product.findUnique({
  where: { slug },
  select: productDetailSelect,
});

Available Select Configurations

Avoiding N+1 Queries

Identifying N+1 Queries

N+1 occurs when you query a list, then query each item individually:

// BAD: N+1 Query Pattern (DO NOT DO THIS)
const orders = await prisma.order.findMany();
for (const order of orders) {
  // This runs N additional queries!
  order.user = await prisma.user.findUnique({
    where: { id: order.userId },
  });
}

Solutions

Solution 1: Use Include

// GOOD: Single query with join
const orders = await prisma.order.findMany({
  include: {
    user: {
      select: userProfileSelect,
    },
  },
});

Solution 2: Use DataLoader

For cases where you need to load the same relation multiple times:

import { createUserLoader } from '@/lib/database';

const userLoader = createUserLoader();

// Batches all user lookups into single query
const ordersWithUsers = await Promise.all(
  orders.map(async (order) => ({
    ...order,
    user: await userLoader.load(order.userId),
  }))
);

Solution 3: Parallel Queries

When relations are independent:

const [products, categories, reviews] = await Promise.all([
  prisma.product.findMany({ where: { categoryId } }),
  prisma.category.findMany(),
  prisma.review.findMany({ where: { productId: { in: productIds } } }),
]);

When to Use Each Pattern

Using DataLoaders

Available Loaders

import {
  createCategoryLoader,
  createProductLoader,
  createReviewCountLoader,
  createReviewAverageLoader,
  createUserLoader,
} from '@/lib/database';

Creating a Request-Scoped Context

// Create loaders once per request
export function createLoaderContext() {
  return {
    categories: createCategoryLoader(),
    products: createProductLoader(),
    reviewCounts: createReviewCountLoader(),
    users: createUserLoader(),
  };
}

// Use in API route or page
const loaders = createLoaderContext();

// All these batch into single queries
const [category1, category2, category3] = await Promise.all([
  loaders.categories.load(1),
  loaders.categories.load(2),
  loaders.categories.load(3),
]);

Pre-built Optimized Queries

import { loadProductsWithStats, loadCategoryTree } from '@/lib/database';

// Load products with category and review stats (4 parallel queries)
const products = await loadProductsWithStats([1, 2, 3, 4, 5]);

// Load category tree with product counts (single query)
const categories = await loadCategoryTree();

Query Caching

When to Cache

Using Next.js Cache

import { getCachedCategories, getCachedProduct } from '@/lib/database/cache';

// Cached for 1 hour, shared across requests
const categories = await getCachedCategories();

// Cached for 1 minute per product
const product = await getCachedProduct('product-slug');

Cache Invalidation

import { revalidateTag } from 'next/cache';

// After updating a product
await prisma.product.update({ ... });
revalidateTag('products');

// After updating categories
await prisma.category.update({ ... });
revalidateTag('categories');

Index Optimization

Essential Indexes

Every table should have indexes for:

1. Primary key (automatic)
2. Foreign keys used in joins
3. Fields used in WHERE clauses
4. Fields used in ORDER BY

Composite Indexes

Order matters! Put most selective column first:

model Product {
  // Good: categoryId is more selective than isActive
  @@index([categoryId, isActive, createdAt])

  // For price range queries
  @@index([isActive, price])
}

Analyzing Query Performance

// Enable query logging in development
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Or use the query monitoring middleware
import { enableQueryMonitoring } from '@/lib/database/monitor';
enableQueryMonitoring();

Performance Thresholds

Target query times:

Common Anti-Patterns

1. Unbounded Queries

// BAD: No limit
const allProducts = await prisma.product.findMany();

// GOOD: Always paginate
const products = await prisma.product.findMany({
  take: 20,
  skip: page * 20,
});

2. Over-including

// BAD: Including everything
const order = await prisma.order.findUnique({
  include: {
    user: true,
    items: {
      include: {
        product: {
          include: {
            category: true,
            reviews: true,
            images: true,
          },
        },
      },
    },
  },
});

// GOOD: Include only what's needed
const order = await prisma.order.findUnique({
  select: orderDetailSelect,
});

3. Querying in Loops

// BAD: Query in loop
for (const id of productIds) {
  const product = await prisma.product.findUnique({ where: { id } });
}

// GOOD: Single query
const products = await prisma.product.findMany({
  where: { id: { in: productIds } },
});

Related Documentation

• Database Migrations Guide
• Prisma Best Practices
• DataLoader Pattern