OPTIMIZATION OBJECTIVE
Find why the order-search query behind GET /orders takes four seconds.
Optimization goal: query speed — find where the time goes and remove the expensive work.
Establish why this query is expensive before changing anything. Every recommendation must trace to evidence from this query, this plan, or this schema — no generic database advice.
DATABASE CONTEXT
Platform: not specified — keep recommendations standard-SQL where possible, and mark every platform-dependent claim as such.
- Where behavior differs by engine (index types, plan tooling, optimizer features), name which engines the advice applies to.
QUERY CONTEXT
Query:
```sql
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE YEAR(o.created_at) = 2026
AND c.region LIKE '%west%'
ORDER BY o.created_at DESC;
```
Execution plan: not provided. Do not invent one. State the exact command to capture it — your engine's actual-execution-plan facility — and mark every plan-dependent conclusion as pending that evidence.
Tables and row counts: not provided — do not assume row counts. State the cardinality questions that block firm conclusions.
Existing indexes: not provided — do not assume any index exists. Before recommending new indexes, list what must be checked about the current ones.
PERFORMANCE SYMPTOMS
P95 latency 4.2 seconds; worst with broad date ranges; fine on the staging copy with less data.
OPTIMIZATION GOAL
Primary goal: Query Speed.
Analysis priorities for this goal:
1. Identify the expensive operations first: full scans, large sorts, spools, repeated subquery evaluation.
2. Check whether every filter is sargable — functions, casts, and leading wildcards on filtered columns defeat index use.
3. Question every sort: what ordered it (ORDER BY, DISTINCT, GROUP BY, a merge join) and whether an index could provide the order for free.
4. Reduce rows as early as possible: the cheapest row is the one never read.
EVIDENCE REVIEW
Evidence mode: Fast Review.
- Move quickly: pattern-level findings are allowed, but each is labeled unverified and paired with the check that would confirm it.
- Name the single most likely bottleneck first, with the fastest way to confirm or kill it.
BOTTLENECK ANALYSIS
- Identify the bottlenecks and, for each: what it costs, why it matters for this workload, and the estimated share of the total cost.
- Expected analysis areas for this goal: Scan vs seek on every table access; Sort and memory pressure; Sargability of every predicate; Rows flowing between operators.
- Distinguish the bottleneck from its symptom — a slow sort caused by a missing filter is a filter problem, not a sort problem.
OPTIMIZATION OPPORTUNITIES
- Order opportunities by expected impact, highest first; state the basis for each estimate.
- For each opportunity: the change, the expected effect, and the evidence that predicts it.
- Any query rewrite must return identical results — call out every difference in NULL handling, duplicates, and ordering, or state explicitly that there is none.
- Do not tune speculatively: a change without an evidenced problem is risk without benefit.
INDEX RECOMMENDATIONS
- Every index recommendation must name the exact predicates, joins, or sorts it serves — no index without a clause.
- Justify column order for composite indexes, and say whether the index should cover (and at what storage and write cost).
- State each index's write tax: which inserts and updates it slows, and whether the workload can afford that.
- Check existing indexes first: a near-miss index that could be extended beats a new overlapping one.
TRADEOFF ANALYSIS
- Every recommendation carries its costs: write amplification, storage, maintenance burden, plan-stability risk, staleness (for pre-aggregation).
- State when NOT to apply each recommendation — the workload shape under which it backfires.
ASSUMPTIONS
- List every assumption made about data volumes, value distributions, index state, or workload patterns.
- Mark each assumption VERIFIED (with its evidence) or UNVERIFIED (with the query or command that would resolve it).
- Any recommendation that depends on an UNVERIFIED assumption must be flagged as conditional on it.
NON-GOALS
- Do not invent execution plans.
- Do not assume indexes exist.
- Do not assume row counts.
- Do not recommend changes without justification.
- Separate facts from assumptions throughout.
- Explain the tradeoffs of every change that has them.
- Schema redesign and application-level changes are out of scope — unless the evidence shows no query-level fix exists, in which case say so and stop.
OUTPUT REQUIREMENTS
- Present recommendations as an ordered list, highest expected impact first, each with its evidence and its tradeoffs.
- For each recommendation, include the verification step: how to measure the improvement — your engine's actual-execution-plan facility before and after, on production-shaped data.
- Where essential evidence is missing, the first recommendations are the commands to gather it — not guesses in its place.
- End with the open questions: what could not be determined from the provided context, and what would settle each.