Query Optimization with Claude Code: Fix N+1, Slow Queries, and Missing Indexes

"Why is production slow?" — Nine times out of ten it's N+1 queries, missing indexes, or SELECT * returning columns nobody asked for. Claude Code generates query optimization strategies when you encode the rules in CLAUDE.md.

---

CLAUDE.md for Query Optimization

## Database Query Rules

### N+1 Prevention
- No DB queries inside loops in list endpoints
- Use Prisma include/select for related data
- Use DataLoader or findMany for bulk fetching
- Never SELECT * — always specify columns with select

### Pagination
- All list endpoints must have limit/cursor pagination
- Default limit: 20, max: 100
- Use cursor-based pagination for large tables (not offset)

### Index Strategy
- Add index on every column used in WHERE
- Composite index for multi-column WHERE (order matters — high-cardinality first)
- @@unique automatically creates an index (no duplicate needed)

### Performance Monitoring
- Log all queries over 500ms to application logger
- Slack alert for queries over 2000ms
- Run EXPLAIN ANALYZE before merging any query touching 100k+ rows

---

N+1 Fix 1: Count Orders Per User

Bad — N queries (one per user):

// PROBLEM: fires one COUNT query per user
const users = await prisma.user.findMany();
for (const user of users) {
  user.orderCount = await prisma.order.count({
    where: { userId: user.id },
  });
}

Fix — 1 query using Prisma _count:

const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    _count: {
      select: { orders: true },
    },
  },
  take: 20,
  ...(cursor && { skip: 1, cursor: { id: cursor } }),
});

// Access: user._count.orders

One query replaces N. _count inside select tells Prisma to emit a single JOIN instead of N round trips.

---

N+1 Fix 2: Posts With Authors

Bad — N queries (one findUnique per post):

// PROBLEM: separate author lookup per post
const posts = await prisma.post.findMany({ take: 20 });
for (const post of posts) {
  post.author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

Fix — 1 query with include:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    publishedAt: true,
    author: {
      select: {
        id: true,
        name: true,
        avatarUrl: true,
      },
    },
  },
  where: { publishedAt: { not: null } },
  orderBy: { publishedAt: 'desc' },
  take: 20,
  ...(cursor && { skip: 1, cursor: { id: cursor } }),
});

Prisma rewrites this as a single JOIN query. No loop, no N round trips.

---

Slow Query Detection: Prisma Middleware

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';
import { logger } from './logger';
import { notifySlack } from './slack';

const prisma = new PrismaClient();

prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const duration = Date.now() - start;

  if (duration > 2000) {
    await notifySlack(
      `Slow query alert: ${params.model}.${params.action} took ${duration}ms`
    );
  } else if (duration > 500) {
    logger.warn({
      model: params.model,
      action: params.action,
      duration,
      msg: 'Slow query detected',
    });
  }

  return result;
});

export { prisma };

Every query over 500ms hits the logger. Over 2000ms triggers a Slack alert. No manual instrumentation per query.

---

EXPLAIN ANALYZE Helper

// src/lib/explainAnalyze.ts
import { prisma } from './prisma';

export async function explainAnalyze(sql: string, params: unknown[] = []) {
  const result = await prisma.$queryRawUnsafe<unknown[]>(
    `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`,
    ...params
  );
  return result[0];
}

// Usage before merging a new query:
// const plan = await explainAnalyze(
//   'SELECT * FROM orders WHERE user_id = $1 AND status = $2',
//   [userId, 'pending']
// );
// console.log(JSON.stringify(plan, null, 2));

Run this in a migration PR to catch sequential scans on large tables before they hit production.

---

Prisma Schema Index Design

model Order {
  id        String   @id @default(cuid())
  userId    String
  status    String
  createdAt DateTime @default(now())

  user User @relation(fields: [userId], references: [id])

  // Composite index: WHERE userId + status (most queries filter both)
  @@index([userId, status])

  // Descending index for ORDER BY createdAt DESC
  @@index([createdAt(sort: Desc)])
}

model UserEmail {
  id     String @id @default(cuid())
  userId String
  email  String

  // @@unique auto-creates an index — no separate @@index needed
  @@unique([userId, email])
}

Rule of thumb: add @@index for every column combination that appears in a WHERE clause. @@unique already creates an index — don't duplicate it.

---

Summary

Four levers for query optimization with Claude Code:

1. CLAUDE.md — encode N+1 rules, SELECT rules, index strategy
2. include / _count — eliminate N+1 in one query
3. Slow query middleware — catch regressions before users report them
4. EXPLAIN ANALYZE — validate indexes before merging

---

Code Review Pack (¥980) includes /code-review — catches N+1 patterns, missing indexes, missing pagination, and unsafe $queryRaw usage in one pass.

👉 prompt-works.jp

Myouga (@myougatheaxo) — Claude Code engineer focused on backend performance.