Database Performance and Indexes in Prisma ORM

A query that takes 3 seconds with 10,000 rows will take 30 seconds with 100,000 rows. Without an index, the database scans every single row to find what you need. That's a full table scan, and it doesn't scale.

An index is like a book's table of contents. Instead of reading every page to find a topic, you look up the page number and jump straight there. Databases work the same way -- indexes create sorted lookup structures that point directly to the rows you need.

Indexes in Prisma

Prisma makes index creation declarative. You define them right in your schema file.

Single-field index -- speeds up queries that filter or sort by one column:

model User {
  id    String @id @default(cuid())
  name  String
  email String @unique
  age   Int

  @@index([email])
}

Multi-field (composite) index -- speeds up queries that filter on multiple columns together:

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

  @@index([userId, status])
  @@index([status, createdAt])
}

The order of fields in a composite index matters. An index on [userId, status] works for queries filtering by userId alone, or userId + status. It does not help queries filtering only by status. Put the most selective field first.

What to Index

Index your foreign keys. Every userId, orderId, or relation field should have an index. Prisma creates them automatically for @relation fields, but verify your migration files.

Index your WHERE clauses. Look at your most frequent queries. Whatever columns appear in WHERE, ORDER BY, or GROUP BY are candidates.

Index your unique constraints. Use @unique in Prisma -- it creates an index automatically.

What Not to Index

Don't index everything. Each index costs storage and slows down writes. Every INSERT, UPDATE, and DELETE must update every index on that table.

Skip low-cardinality columns. A boolean column with only true/false values won't benefit much from an index. The database can't narrow down results effectively.

Skip columns you rarely query. An index that's never used is pure overhead.

Finding Slow Queries

Indexes don't help if you don't know what's slow. Use Prisma's logging to find problem queries:

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

prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.log(`Slow query (${e.duration}ms): ${e.query}`);
  }
});

Then use EXPLAIN ANALYZE on the underlying SQL to see if the database is using your indexes or falling back to sequential scans.

The Trade-off

Indexes make reads faster and writes slower. For read-heavy applications (which most web apps are), this is a good trade. For write-heavy workloads like logging or event streaming, too many indexes can become a bottleneck.

The goal isn't maximum indexes. It's the right indexes for your query patterns.