Indexing for Fast Reads¶

Reads dominate most apps. An index is what makes filtering, sorting, and joining fast — without one, the database scans every row.

What an Index Is¶

Like the index at the back of a book: instead of reading every page to find "CRUD", you jump straight to it. A database index is a sorted structure (usually a B-tree) that lets the engine seek instead of scan.

1CREATE INDEX tasks_owner_id_idx ON tasks (owner_id);

Index What You Filter, Sort, and Join On¶

• Foreign keys — owner_id (you filter and join on it constantly).
• Columns in WHERE — status, due_date.
• Columns in ORDER BY — especially for cursor pagination.

1model Task {
2  // ...
3  @@index([ownerId])
4  @@index([ownerId, status])      // composite, for "my TODO tasks"
5  @@index([createdAt, id])        // supports keyset pagination order
6}

Composite Index Column Order Matters¶

An index on (ownerId, status) speeds up filters on ownerId alone, or ownerId AND status — but not status alone. Put the most selective / always-present column first (the "leftmost prefix" rule).

Unique Indexes Enforce Rules¶

1email String @unique   // also a fast lookup index

A unique index both prevents duplicates and accelerates lookups — two wins.

The Cost of Indexes¶

Indexes aren't free: every INSERT/UPDATE/DELETE must also update them, and they consume storage. Don't index every column — index the ones your real queries use.

Measure with EXPLAIN¶

1EXPLAIN ANALYZE SELECT * FROM tasks WHERE owner_id = 7 AND status = 'TODO';

Look for Index Scan (good) vs Seq Scan (the whole table — bad on large data). EXPLAIN turns "it feels slow" into evidence about why.