Group
  • Database

    Add the indexes my queries actually need

    Reads your real queries, finds the lookups doing full collection scans, and adds the right indexes.

    • Improve quality
    • Debug & fix
    • Intermediate
    • General

Free Prompt

Audit my database queries and add the indexes they need. Read through my actual query code (not just the schema): every find, filter, sort, join, and lookup my app runs. For each query pattern, identify the fields it filters and sorts on, then check whether an index covers it. Add indexes for: fields used in WHERE clauses on every request (user_id on owned records is the big one), fields used for sorting list pages (created_at, updated_at), unique lookups (email, slug, username), and foreign keys used in joins or lookups. Where a query filters on two fields together, create a compound index in the right order (equality fields first, then range/sort fields) rather than two separate ones. Do not index every field; each index costs write performance and storage, so justify each one by the query it serves. Do not add duplicate indexes that an existing compound index already covers. If my database supports it, show me the query plan (EXPLAIN) before and after for the slowest queries. Deliver a table: query, fields used, index added, and the expected improvement. Flag any query that can't be fixed with an index alone (like a text search doing substring matching) and note it for a separate fix.

What This Does / How This Helps

Reads your real query code, finds which lookups are scanning entire tables, and adds indexes matched to how the app actually queries. Missing indexes are invisible in development. With fifty rows in the database everything is instant, so the app ships, real data arrives, and page loads climb from 50ms to five seconds as every query reads the whole table. The user_id index alone fixes the most common case: every 'show me my stuff' query scanning everyone else's rows to find yours. You get a per-query justification for each index, compound indexes ordered correctly where queries filter on multiple fields, and before/after query plans where the database supports them.

Want to skip doing this by hand?

Fortivibe audits your app for all of the areas these prompts cover (and more).

See What We Check

Related Prompts