Map every predicate, join, and sort to the index that serves it — or doesn't. Composite order rules, covering decisions, and the write tax nobody mentions.
Missing-index analysis goes wrong in both directions: indexes that were never needed, and indexes designed with the wrong column order that never get used. This prompt uses the index strategy goal: map every predicate, join column, and sort to the index (or absence) that serves it; design composites by the rules — equality columns first, then ranges, then order-by, because column order decides usability; consider covering with its storage and maintenance price; and count the cost honestly — every index taxes every write, and an index serving one slow report may not be worth slowing every insert. With no index list provided, the contract refuses to assume: checking what exists is step one.
Check what exists first
No assumed indexes: the current state is evidence to gather, and near-miss indexes may just need extending.
Map clauses to indexes
Every predicate, join, and sort accounted for — no index without a clause it serves.
Pay the write tax knowingly
Each candidate states which inserts and updates it slows — and whether the workload affords it.
It applies one ordering rule: equality columns first, then ranges, then order-by. For the customers lookup, region and status are equality predicates, so they lead; last_order_at drives the ORDER BY DESC, so it trails. The prompt justifies that sequence because column order decides whether the index gets used at all, not merely how large it is.
When its write tax outweighs the read gain. The prompt's cost rule flags that every index slows every insert, so an index serving one slow report may not justify taxing every write. Each candidate must state which inserts and updates it slows and weigh that against write amplification, storage, and maintenance before it recommends anything.
It can, but it refuses to assume any index or plan exists. With no index list, step one becomes checking the current ones, since a near-miss index that could be extended beats a new overlapping one; with no plan, it hands you the command to capture your engine's actual-execution-plan facility. Plan-dependent conclusions stay flagged pending that evidence.
A five-table join that got slow as data grew: establish cardinality first, check each join strategy against the data shape, and hunt the fan-out doing wasted work.
"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.
Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.
"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.
"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.
"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.
Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.
The full path from idea to a shipped SaaS MVP — define and scope the requirements, design the architecture, API, and data model, then build it reviewed, tested, secured, cost-controlled, and deployed.
The full path to a backend you can put clients on — define the requirements, design the architecture, API contract, data model, and access control, then build it reviewed, tested, secured, and shipped.
The full path to a two-sided platform — define the buyer-and-seller requirements, model the data, design the API, build roles and permissions, wire integrations, design the UI, then test, secure, and ship it.
The full path to a CRM that fits your sales process — define the contacts, deals, and pipeline, model the data that ties them together, then build the roles, integrations, and pipeline UI, and ship.
The full path to a store you own end to end — model the catalog and orders, design the storefront and checkout, add customer accounts and payments, then secure it, test it, and ship.
The full path to a pipeline that moves data without corrupting it — design the ingestion and transforms, extract and structure the sources, gate the quality, store it, then deliver and ship it monitored.
Design a schema on its data, not a hunch — model the entities and relationships, set the constraints that protect integrity, plan indexes around real queries, then document the schema and migration.
Ask AI to "make this query faster" and it guesses — it never saw your execution plan and doesn't know your indexes or row counts. Optimize a slow SQL query with AI instead: bring the evidence, diagnose before you change, and prove the result before it ships.