Indexing Strategy Recommendation Prompt

This prompt is designed for database engineers and backend developers who need to move beyond generic indexing advice and produce a prioritized, production-ready set of index recommendations. The core job-to-be-done is taking a concrete CREATE TABLE statement and a representative sample of SELECT, UPDATE, and DELETE queries, then generating a list of proposed indexes that balances query performance against write amplification. It is most effective when used during schema review, before writing a migration script, or when diagnosing a specific slow query pattern where the current indexing strategy is insufficient. The ideal user has the actual DDL and can provide real query patterns from application code, an observability platform, or a slow-query log.

The prompt forces the model to reason about trade-offs rather than suggesting every possible index. It requires the model to consider covering indexes to avoid key lookups, the write cost of maintaining additional indexes on high-throughput tables, and the migration cost of creating an index on a large production table. Do not use this prompt for abstract database theory questions, for schemas where you cannot provide actual query samples, or when you need a real-time performance diagnosis from a running system. It is a design-time reasoning tool, not a replacement for EXPLAIN ANALYZE or a database monitoring platform. The output is a structured recommendation list, not executable SQL, and should always be reviewed by an engineer who understands the specific database system, version, and workload characteristics.

Before using this prompt, gather the exact DDL for the table in question, including all existing indexes and constraints. Collect a representative set of query patterns, parameterized to hide sensitive values but preserving the WHERE clause structure, JOIN conditions, ORDER BY clauses, and approximate frequency. If you lack actual query samples, stop here—the prompt will produce unreliable, generic advice without them. After receiving the model's output, validate each recommendation against your database's specific B-tree, hash, GIN, or GiST index capabilities. For high-risk production tables, always test the recommended indexes in a staging environment with a comparable data volume and query load before applying them to production. The next step is to take the prioritized list and write a migration script, but only after an engineer has confirmed the recommendations are safe and appropriate for your specific operational context.

This prompt template is designed to be pasted directly into your AI system. It instructs the model to act as a database performance engineer, analyzing provided query patterns and table schemas to produce a structured, prioritized set of index recommendations. The output is designed to be actionable, including the index definition, expected impact, and potential risks like write amplification. Replace the square-bracket placeholders with your specific database context, query logs, and constraints before execution.

textCopy
You are a database performance engineer specializing in indexing strategy. Your task is to analyze the provided table schemas and query patterns to recommend a set of indexes. Follow the instructions precisely.

# INPUT DATA

## Table Schemas
[TABLE_SCHEMAS]

## Query Patterns
[QUERY_PATTERNS]

# CONSTRAINTS
- Target database system: [DATABASE_SYSTEM]
- Maximum recommended indexes per table: [MAX_INDEXES_PER_TABLE]
- Write-to-read ratio: [WRITE_READ_RATIO]
- Storage budget (if any): [STORAGE_BUDGET]

# OUTPUT SCHEMA
Produce a JSON object with a single key "recommendations", which is an array of objects. Each object must have the following fields:
- "table_name": (string) The name of the table.
- "proposed_index": (string) The exact DDL statement to create the index.
- "index_type": (string) e.g., "BTREE", "HASH", "GIN", "partial", "composite".
- "target_queries": (array of strings) The specific query patterns from the input that this index will improve.
- "expected_impact": (string) A concise explanation of how the index will improve performance (e.g., "Enables Index-Only Scan for query #3, avoiding full table scan").
- "concerns": (array of strings) A list of potential downsides, such as write amplification, large index size, or low selectivity.
- "priority": (string) One of "HIGH", "MEDIUM", or "LOW".

# INSTRUCTIONS
1. Analyze the `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY` clauses in the query patterns.
2. Identify columns used for filtering, sorting, and joining that are not already part of a primary key or existing index.
3. Prioritize indexes that cover the most frequent and expensive queries, as indicated by the `WRITE_READ_RATIO`.
4. Explicitly flag any recommendation that could cause significant write amplification.
5. If no beneficial indexes can be found, return an empty array for "recommendations".
6. Do not recommend redundant indexes.

To adapt this prompt, start by populating [TABLE_SCHEMAS] with the output of your database's SHOW CREATE TABLE or equivalent DDL. For [QUERY_PATTERNS], use sanitized, parameterized versions of your slow query logs or application's most critical access patterns. The [WRITE_READ_RATIO] is crucial; a write-heavy workload should result in fewer, more targeted index recommendations. After receiving the output, validate the JSON structure against the defined schema before considering implementation. For high-risk production databases, always require a human DBA to review the proposed proposed_index DDL and concerns before executing any changes.

The indexing strategy prompt is not a standalone advisor—it is a component in a database change management workflow. Wire it into your application as a structured recommendation generator that produces machine-readable output for downstream validation, comparison, and approval. The prompt expects a DDL schema and a set of query patterns as input, and it returns a prioritized list of proposed indexes with impact estimates, migration costs, and risk flags. Treat the output as a draft recommendation, not a final execution plan.

Model choice and invocation: Use a model with strong SQL reasoning capabilities (GPT-4o, Claude 3.5 Sonnet, or equivalent). Set temperature to 0 or near-zero to maximize deterministic, repeatable recommendations. The prompt should be called with structured input containing [SCHEMA_DDL] and [QUERY_PATTERNS] as separate fields. Parse the JSON output into your application's index review queue. Validate that every recommended index includes a table_name, columns array, index_type, estimated_impact, and migration_cost field. Reject and retry any response that fails schema validation—do not pass incomplete recommendations downstream.

Safety and over-indexing guardrails: Before surfacing recommendations, run automated checks against the output. Count the total number of proposed indexes per table and flag any table receiving more than a configurable threshold (start with 5). Check for duplicate or prefix-redundant indexes (e.g., an index on (a, b) makes a separate index on (a) redundant for many query patterns). Cross-reference the write_amplification_risk field in each recommendation—if the prompt flags high risk but still recommends the index, escalate for human review. Log every recommendation with the input schema hash, query pattern fingerprint, model version, and timestamp for auditability.

Human-in-the-loop integration: Route all recommendations through a review queue before execution. For low-risk recommendations (low write amplification, low migration cost, clear query coverage), allow auto-approval with a notification. For high-risk or high-cost recommendations, require explicit DBA or engineering lead approval. Include a diff view showing the proposed index DDL alongside the existing schema. The prompt's covering_concerns field should be displayed to reviewers so they understand the trade-offs already considered. Never auto-execute CREATE INDEX statements directly from model output without human review in production environments.

Retry and failure handling: If the model returns malformed JSON, retry once with a repair prompt that includes the raw output and the expected schema. If the second attempt fails, log the failure and alert the engineering team—do not silently drop the recommendation request. If the model returns an empty recommendations array when query patterns clearly suggest missing indexes, flag this as a potential false negative and escalate. Monitor the rate of accepted vs. rejected recommendations over time to detect model drift or schema changes that degrade recommendation quality.