Indexes

An index is a lookup structure that lets the database find rows without scanning the entire table. Without one, finding a user by email means checking every single row. It's the index at the back of a book. Instead of reading every page to find "transactions," you jump straight to the right page number.

How to use it

model User {
  id    Int    @id @default(autoincrement())
  email String

  @@index([email]) // look users up by email quickly
}

By default this creates a B-tree index, the same structure the database uses for primary keys.

Index types

Most databases offer more than one kind of index. Choosing the perfect one for a given query is out of scope here, but it's worth knowing the options exist:

• B-tree (the default) — keeps keys sorted, so it handles equality (=), ranges (<, >, BETWEEN), and ORDER BY. This is what you want the vast majority of the time.
• Hash — handles equality (=) only, but can be smaller and a touch faster for that one job. No range or sort support.
• Specialized (GIN, GiST, and friends) — for full-text search, JSON, and geospatial data. Just know they exist; reach for them when you hit those cases.

Prisma lets you pick the type explicitly:

model Session {
  id    Int    @id @default(autoincrement())
  token String

  @@index([token], type: Hash) // equality lookups only, never ranges
}

The tradeoff

Indexes make reads fast but aren't free:

• Every write has to update the index too, so writes get slightly slower
• Each index takes up disk space

Index the columns you actually search, sort, or join on. Not every column.