"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.
Most SQL optimization prompts get answers that would read the same for any query: add an index, avoid SELECT *. The optimization contract structures the analysis instead: bottlenecks identified with their cost share, opportunities ordered by evidenced impact, index recommendations that must name the exact clause they serve and the writes they slow, tradeoffs for every change, and an assumptions ledger — with the non-goals that keep it honest: do not invent execution plans, do not assume indexes exist, do not assume row counts. This setup loads a slow API query whose SQL carries two classic sins — SELECT * and a non-sargable YEAR() filter — for the contract to work on.
Bring the evidence you have
Query, plan, row counts, indexes, measured symptoms — the contract adapts to what is provided and refuses to fake the rest.
Hold the analysis order
Bottlenecks with cost shares first, opportunities by evidenced impact second — never recommendations before diagnosis.
Check every index's pedigree
Each recommendation names the predicates it serves, justifies its column order, and states its write tax.
The NON-GOALS forbid it: do not invent execution plans, do not assume indexes exist, do not assume row counts. When the plan is "not provided", the contract instead states the exact command to capture your engine's actual-execution-plan facility and marks every plan-dependent conclusion as pending that evidence. The sql-optimization-prompt generates this; you run it in your assistant and gather the missing evidence yourself.
No index without a clause: each must name the exact predicates, joins, or sorts it serves, justify composite column order, and state its write tax, which inserts and updates it slows. INDEX RECOMMENDATIONS also checks existing indexes first, preferring to extend a near-miss. On the loaded query, the non-sargable YEAR(o.created_at) filter and SELECT * are the evidenced targets. You validate each on production-shaped data.
Through the ASSUMPTIONS ledger: every assumption about data volumes, distributions, or index state is marked VERIFIED (with its evidence) or UNVERIFIED (with the query that would resolve it), and any recommendation depending on an UNVERIFIED assumption is flagged conditional. In Fast Review mode, pattern-level findings are labeled unverified with the check to confirm them. The tool structures this; confirming each assumption is your step.
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 what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.
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.
"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.