Claude Prompt to Design a Database Indexing Strategy

What this prompt does

This prompt makes the model act as a database indexing expert. You give it [database_engine], your [table_schema], your [query_patterns], the [table_size], and the [write_read_ratio], and it produces a full indexing strategy rather than a single quick suggestion. It audits existing indexes, analyzes each query pattern, recommends new indexes with CREATE INDEX statements, explains column ordering, lays out trade-offs, and gives a maintenance plan.

The structure forces the model to show its reasoning. For each query it walks through what the planner would do under EXPLAIN ANALYZE, whether a sequential scan is happening, and whether the column is selective enough to benefit from an index. Because you supply the [write_read_ratio], it can weigh read speedups against write overhead — an index that helps an 80%-read table may be wrong on a write-heavy one. The [additional_index_type] slot lets you nudge it toward full-text, GIN, or other specialized indexes your workload needs.

When to use it

• When an app slows down and you suspect missing or wrong indexes rather than insufficient hardware.
• Before adding an index to a large table, to confirm it actually helps the queries you run.
• To find redundant indexes (where one is a prefix of another) you can safely drop.
• When you need correct composite-index column order for multi-condition queries.
• To plan a safe, non-locking index rollout on a big production table.

Example output

Expect a sectioned report: an audit listing current indexes and their types, a per-query analysis with planner reasoning and estimated cost before/after, a set of recommended CREATE INDEX statements each annotated with the reason, the queries it helps, estimated speedup, and storage cost. It includes a trade-off table (read improvement vs. write overhead vs. storage, with an Add/Skip/Defer call) and a maintenance plan covering reindex schedules, bloat monitoring, and online DDL.

Pro tips

• Paste your actual [table_schema] including existing indexes — the audit can only spot redundancy if it sees what you already have.
• Give it 5-10 real [query_patterns], the ones that run most often, not hypothetical queries. Index quality is only as good as the workload you describe.
• Set [write_read_ratio] honestly. On write-heavy tables I expect it to recommend fewer indexes, because every index taxes inserts and updates.
• Match [database_engine] exactly (MySQL 8.0 vs. Postgres 15) so the CREATE INDEX syntax and online-DDL advice (CONCURRENTLY vs. online DDL) are correct.
• Treat the "estimated improvement" numbers as directional, not measured — always benchmark the suggested indexes on a copy of production before shipping.
• Use [additional_index_type] to request specialized indexes (full-text, partial, expression) when your queries filter on computed columns or search text.