Optimizing Database Queries with Prisma

The Performance Problem

As our platform grew, certain pages became noticeably slow. Database query analysis revealed N+1 queries and missing indexes as the main culprits.

Technique 1: Eager Loading with Include

Instead of fetching related data in separate queries:

// Before: N+1 queries
const posts = await prisma.blog_posts.findMany();
for (const post of posts) {
  post.author = await prisma.users.findUnique({ where: { id: post.authorId }});
}

// After: Single query with join
const posts = await prisma.blog_posts.findMany({
  include: {
    users: { select: { id: true, name: true, avatar: true }},
    blog_categories: true,
  },
});

Technique 2: Select Only What You Need

Don't fetch entire rows when you only need a few fields:

// Only fetch the fields we display
const users = await prisma.users.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    role: true,
    // Omit passwordHash, twoFactorSecret, etc.
  },
});

Technique 3: Strategic Indexes

We added indexes for frequently queried columns:

model blog_posts {
  // ...fields
  @@index([authorId])
  @@index([categoryId])
  @@index([featured, publishedAt])
}

Results

Dashboard load time dropped from 2.3s to 400ms. The techniques are simple but the impact is significant.