Database Query Optimization

// Prisma schema with strategic indexes
// schema.prisma

// model Product {
//   id          String   @id @default(cuid())
//   name        String
//   slug        String   @unique
//   price       Decimal
//   categoryId  String
//   status      String   @default("active")
//   createdAt   DateTime @default(now())
//
//   category    Category @relation(fields: [categoryId], references: [id])
//
//   // Composite index for common query patterns
//   @@index([categoryId, status, price])  // Category listing sorted by price
//   @@index([status, createdAt])          // Recent active products
//   @@index([name], type: FullTextIndex)  // Full-text search
// }

// Efficient query with Prisma
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' }, // Log all queries
  ],
});

// Log query performance
prisma.$on('query', (e) => {
  if (e.duration > 100) { // Log slow queries (>100ms)
    console.warn(`Slow query (${e.duration}ms): ${e.query}`);
  }
});

// GOOD: Select only needed fields, use index-friendly filtering
async function getProductListing(categoryId: string, page: number) {
  return prisma.product.findMany({
    where: {
      categoryId,
      status: 'active',
    },
    select: {
      id: true,
      name: true,
      slug: true,
      price: true,
      // Don't select large text fields for listing pages
    },
    orderBy: { price: 'asc' },
    take: 20,
    skip: (page - 1) * 20,
  });
}

// BAD: N+1 problem
async function getOrdersWithItemsBad() {
  const orders = await prisma.order.findMany();
  // This executes N additional queries — one per order!
  for (const order of orders) {
    order.items = await prisma.orderItem.findMany({
      where: { orderId: order.id },
    });
  }
  return orders;
}

// GOOD: Single query with includes
async function getOrdersWithItemsGood() {
  return prisma.order.findMany({
    include: {
      items: {
        select: { id: true, productName: true, quantity: true, price: true },
      },
    },
    take: 50,
  });
}

// Analyzing PostgreSQL query execution plans
// Run EXPLAIN ANALYZE to see actual execution stats

// Raw SQL query analysis helper
async function analyzeQuery(sql: string): Promise<string> {
  const result = await prisma.$queryRawUnsafe(
    `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`
  );
  return JSON.stringify(result, null, 2);
}

// Common EXPLAIN output indicators:
// - Seq Scan: Full table scan (bad for large tables — add an index)
// - Index Scan: Using an index (good)
// - Index Only Scan: Covering index, no table lookup (best)
// - Nested Loop: Join strategy, fine for small result sets
// - Hash Join: Better for larger joins
// - Sort: Expensive if not using an index; check for missing index on ORDER BY column

// SQL optimization examples:
// 
// BAD: Full table scan because of function on indexed column
// SELECT * FROM products WHERE LOWER(name) = 'widget';
//
// GOOD: Use a functional index or store lowercase
// CREATE INDEX idx_products_name_lower ON products (LOWER(name));
//
// BAD: Non-sargable condition prevents index usage
// SELECT * FROM orders WHERE YEAR(created_at) = 2024;
//
// GOOD: Use range comparison
// SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
//
// BAD: OR can prevent index usage
// SELECT * FROM products WHERE category_id = 'abc' OR status = 'featured';
//
// GOOD: Use UNION ALL
// SELECT * FROM products WHERE category_id = 'abc'
// UNION ALL
// SELECT * FROM products WHERE status = 'featured' AND category_id != 'abc';

// Cursor-based pagination is much faster than offset pagination for large datasets
// Offset pagination: O(offset + limit) — gets slower as you paginate deeper
// Cursor pagination: O(limit) — constant performance regardless of page depth

interface PaginatedResult<T> {
  items: T[];
  nextCursor: string | null;
  hasMore: boolean;
}

async function getProductsCursor(
  cursor?: string,
  limit: number = 20
): Promise<PaginatedResult<any>> {
  const items = await prisma.product.findMany({
    where: { status: 'active' },
    take: limit + 1, // Fetch one extra to check if there are more
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1, // Skip the cursor item itself
    }),
    orderBy: { createdAt: 'desc' },
    select: {
      id: true,
      name: true,
      price: true,
      createdAt: true,
    },
  });

  const hasMore = items.length > limit;
  const results = hasMore ? items.slice(0, limit) : items;

  return {
    items: results,
    nextCursor: hasMore ? results[results.length - 1].id : null,
    hasMore,
  };
}

// API route using cursor pagination
// app/api/products/route.ts
export async function GET(request: NextRequest) {
  const cursor = request.nextUrl.searchParams.get('cursor') || undefined;
  const limit = parseInt(request.nextUrl.searchParams.get('limit') || '20');

  const result = await getProductsCursor(cursor, Math.min(limit, 100));

  return NextResponse.json(result, {
    headers: { 'Cache-Control': 'public, s-maxage=30' },
  });
}