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.
Join-heavy queries degrade in a specific way: the join order and strategies that fit yesterday's table sizes stop fitting today's. This prompt uses the join optimization goal — establish cardinality first (which side is small, what each join multiplies or filters), check every join strategy against the data shape (nested loops, hash, merge — each has a shape it serves), hunt fan-out that explodes rows only to collapse them again, and verify join columns are typed identically, because an implicit conversion silently disables index use. The loaded setup provides real row counts (12M orders, 48M items, 40 warehouses) — the cardinality context that join advice is worthless without.
State the sizes
Row counts per table — join analysis without cardinality is guesswork, and the contract says so.
Match strategy to shape
Each join checked: loops for few rows with an index, hash for large unordered sets, merge for sorted inputs.
Hunt the waste
Fan-out, repeated lookups, joined-but-unused tables, implicit conversions — the work that adds rows but not results.
Paste the SQL query and the table row counts — join analysis without cardinality is guesswork, which is why the loaded example ships real counts like 12M orders and 48M order_items. Add the execution plan if you have it; the prompt won't invent one, and it won't assume your indexes or row counts either. Anything missing, it tells you the exact command to capture first.
An ordered list of optimization opportunities, highest expected impact first, each with the evidence that predicts it, its tradeoffs (write amplification, storage, plan-stability), and a verification step — capture the actual execution plan before and after. Index recommendations name the exact predicates they serve, assumptions are marked VERIFIED or UNVERIFIED, and it ends with the open questions the provided context couldn't settle.
This is the join-optimization contract — join order, strategy against the data shape (nested loops, hash, merge), fan-out, and the implicit type conversion that silently disables an index. A single-table filter problem is a different goal. It recommends and evidences changes for you to apply and measure; any rewrite it suggests must return identical results or call out every NULL, duplicate, and ordering difference.
"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.
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.