# Indexes exist on columns used in WHERE, JOIN, and ORDER BY clauses

- **Pattern:** `ab-000899` (`database-design-operations.schema-design.indexes-on-queries`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000899
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000899)

## Why it matters

An unindexed email column means every login attempt performs a full table scan. An unindexed FK column means every JOIN on that relationship scans the entire child table. At 10,000 rows this is slow; at 100,000 rows it causes timeouts. Postgres does not automatically index FK columns — unlike MySQL — so every `*_id` column added by an ORM is unindexed until explicitly declared. ISO 25010 time-behaviour directly captures this: query latency degrades predictably as data volume grows, and the degradation is sharp rather than gradual when no index exists. Missed indexes on `status` or `created_at` columns produce full scans on the most common listing and filtering queries in the application.

## Severity rationale

High because missing indexes cause full table scans on every query of the affected column, producing latency that grows linearly with row count and degrades user experience at scale.

## Remediation

Add `@@index` in Prisma or `CREATE INDEX` in SQL for every FK column, high-cardinality filter column (email, slug, external_id), and sort column (created_at) used in listing endpoints.

```prisma
model Order {
  id     String @id @default(uuid())
  userId String
  status String
  @@index([userId])
  @@index([userId, status])
}
```

```sql
-- Add indexes without locking the table on Postgres
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_posts_created_at ON posts(created_at DESC);
```

For composite queries that filter on two columns together, a composite index beats two single-column indexes — verify query plans with `EXPLAIN ANALYZE` before and after.

## Detection

- **ID:** `indexes-on-queries`
- **Severity:** `high`
- **What to look for:** First, enumerate every column that appears in a WHERE, JOIN, or ORDER BY clause by reading API route files, service files, and repository classes. Identify the most common query patterns by reading API route files, service files, and repository classes. Note every column that appears in a WHERE clause, JOIN condition, or ORDER BY. Then examine the schema for indexes. In Prisma, look for `@@index([...])` and `@unique` decorators. In Drizzle, look for `.index()` and `.unique()` on columns. In raw SQL migrations, look for `CREATE INDEX`. Cross-reference: columns used frequently in queries should have indexes. Common missed indexes: `email` column on users table (used in login lookups), `created_at` on time-ordered tables (used in sorted listing), all foreign key columns (Postgres does NOT automatically index FK columns unlike MySQL), `status` or `state` columns used in filtered listings. Also check for missing composite indexes where queries filter on multiple columns together.
- **Pass criteria:** Columns that appear in WHERE/JOIN/ORDER BY clauses in the most common query paths have indexes — at least 90% of FK columns are indexed. All FK columns are indexed. High-cardinality filter columns (email, external_id, slug) are indexed. Indexes are not redundant (e.g., a unique constraint already provides an index).
- **Fail criteria:** Commonly queried columns lack indexes. FK columns are unindexed (a full table scan on every JOIN). Login flow queries a non-indexed email column. Listing endpoints sort by `created_at` with no index.
- **Skip (N/A) when:** N/A — always applicable for any project with a database (even SQLite benefits from indexes on large-ish tables).
- **Detail on fail:** Specify which columns are missing indexes and the queries that need them. Example: `"'users.email' has no index — used in login query WHERE email = $1 without index support."` or `"'orders.user_id' FK column has no index — JOIN queries will full-scan the orders table."` or `"'posts.created_at' has no index — listing endpoint ORDER BY created_at DESC will sort without index."`.
- **Remediation:** Add indexes for common query patterns:

  ```prisma
  // Prisma — index on email for login lookup
  model User {
    id        String @id @default(uuid())
    email     String @unique  // @unique implies an index
    createdAt DateTime @default(now())

    @@index([createdAt])  // for time-ordered listing
  }

  // Prisma — index on FK column
  model Order {
    id     String @id @default(uuid())
    userId String
    status String

    @@index([userId])          // index the FK
    @@index([userId, status])  // composite index if filtering by both
  }
  ```

  ```sql
  -- Raw SQL — add missing indexes
  CREATE INDEX idx_users_email ON users(email);
  CREATE INDEX idx_orders_user_id ON orders(user_id);
  CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
  CREATE INDEX idx_posts_status_created ON posts(status, created_at DESC);
  ```

  When adding indexes to an existing production table with many rows, use `CREATE INDEX CONCURRENTLY` in Postgres to avoid locking: `CREATE INDEX CONCURRENTLY idx_users_email ON users(email);`

---

## External references

- iso-25010 performance-efficiency.time-behaviour

Taxons: performance

HTML version: https://auditbuffet.com/patterns/ab-000899