Prompt Engineering

MySQL Query Optimization — InnoDB Realities, type=ALL

EXPLAIN shows type=ALL on a 1.2M-row table: full scan on every search. MySQL optimization with InnoDB clustering realities, version-aware optimizer limits, and covering reads.

Open in SQL Optimization Prompt

Overview

MySQL optimization is version-and-engine work: advice that ignores InnoDB's clustering or the optimizer's version timeline misleads. This prompt carries the realities: use EXPLAIN ANALYZE (8.0+) or FORMAT=JSON because the table format hides attached conditions; the primary key is the clustered index and every secondary index carries it, so a big primary key taxes everything; the optimizer's limits are version-specific — no hash joins before 8.0.18, historically weak subquery optimization — so rewrites must be pinned to the version; and covering reads ("Using index" in Extra) separate index-only access from table reads, a large difference on busy tables. The loaded scenario is the classic type=ALL full scan on product search.

How to use this resource

Get past the table format

EXPLAIN ANALYZE or FORMAT=JSON — attached conditions and cost detail live there.
Cost the clustering

Every secondary index carries the primary key — index advice accounts for it.
Pin advice to the version

Hash joins, subquery handling, ICP — what the optimizer can do depends on the release.

Why This Works

Version-pinned advice prevents recommendations the engine cannot execute
InnoDB economics make index costs concrete instead of theoretical
The covering-read lens targets MySQL's biggest cheap win

Best for

MySQL 8 applications with search and listing queries
InnoDB tables whose primary keys grew wide
Teams inheriting version-mixed MySQL fleets

Not for

MariaDB specifics — its optimizer diverged; use the MariaDB platform mode
Generating test data for the queries — that's the Test Case Prompt Generator's world

Use cases

Eliminating type=ALL full scans on search queries
Designing secondary indexes around the InnoDB primary key
Rewriting subqueries the version's optimizer handles badly

FAQ

What happens if I run this MySQL prompt without pasting the EXPLAIN output?

It won't invent a plan. The QUERY CONTEXT says "Execution plan: not provided. Do not invent one," so the generated prompt's first recommendations become the exact commands to capture it — EXPLAIN ANALYZE (8.0+) or EXPLAIN FORMAT=JSON — and it marks every plan-dependent conclusion as pending. You gather that evidence, then run the completed analysis in your own assistant.

Why does the index advice keep mentioning the InnoDB primary key?

Because InnoDB clusters on it: "the primary key is the clustered index and every secondary index carries it — a big primary key taxes every index." So a new secondary index for the type=ALL product search costs the primary key's width on each entry, plus a write tax on every insert. The prompt makes that economics explicit; you verify with EXPLAIN before and after on production-shaped data.

Is this MySQL prompt usable as-is on a MariaDB database?

Not as-is — the notFor line says MariaDB's optimizer diverged and points to the MariaDB platform mode. This prompt pins advice to MySQL specifics like "no hash joins before 8.0.18" and covering reads shown as "Using index" in Extra, which don't transfer cleanly. Regenerate with the MariaDB platform selected so the version-and-engine assumptions match what your database can execute.

Customize This Resource

Opens this setup in SQL Optimization Prompt. Generate to get the full optimization contract — then adjust the goal, platform, and evidence mode.

Open in SQL Optimization Prompt

Prompt Template

Copy it as-is, or use Open in SQL Optimization Prompt to load it pre-filled and customize it with your own context.

OPTIMIZATION OBJECTIVE
Optimize the product search query on MySQL 8 — EXPLAIN shows type=ALL on products.
Optimization goal: index strategy — which indexes this workload needs, and what each one costs.
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: MySQL.
- Use EXPLAIN ANALYZE (8.0+) or EXPLAIN FORMAT=JSON — the table format hides attached conditions and cost detail.
- Assume InnoDB realities: the primary key is the clustered index and every secondary index carries it — a big primary key taxes every index.
- Respect the optimizer's version-specific limits: no hash joins before 8.0.18 and historically weak subquery optimization — rewrite or materialize where the version demands it.
- Chase covering reads: "Using index" in Extra means index-only access — the difference between that and table reads is large on busy tables.

QUERY CONTEXT
Query:
```sql
[Paste the SQL query here]
```
Execution plan: not provided. Do not invent one. State the exact command to capture it — EXPLAIN ANALYZE (8.0+) or EXPLAIN FORMAT=JSON — 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
Full table scan on every search; 1.2M products; search box is the top user complaint.

OPTIMIZATION GOAL
Primary goal: Index Strategy.
Analysis priorities for this goal:
1. Map every predicate, join column, and sort to the index (or the absence) that serves it.
2. Design composite indexes by the rules: equality columns first, then ranges, then order-by — column order decides usability.
3. Consider covering: when including payload columns lets the query skip the table entirely, and when that is too expensive to maintain.
4. Count the cost: every index taxes every write — an index serving one slow report may not be worth slowing every insert.

EVIDENCE REVIEW
Evidence mode: Standard Analysis.
- Prefer evidence from the provided query, plan, and schema; where evidence is missing, label the assumption explicitly.
- Carry at least two candidate bottlenecks until evidence separates them.

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: Predicate-to-index mapping; Composite column order; Covering opportunities; Write cost of each candidate.
- 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 — EXPLAIN ANALYZE (8.0+) or EXPLAIN FORMAT=JSON 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.

More resources from SQL Optimization Prompt

Resource

Optimize SQL Query — Joins, Cardinality, and Wasted Work

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.

Prompt Engineering

Resource

SQL Optimization Prompt — the Evidence-Based Contract

"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.

Prompt Engineering

Resource

Execution Plan Analysis — Read What the Engine Actually Chose

Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.

Prompt Engineering

Resources that pair well

Resource

Debugging Prompt — the Investigation Contract

"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.

Prompt Engineering

Resource

Code Review Prompt — the Review Contract

"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.

Prompt Engineering

Resource

Refactor Prompt — the Behavior Preservation Contract

"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.

Prompt Engineering

Related tools

Tool

SQL Optimization Prompt

Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.

Coding Workflows

Tip: Save time by exploring related resources and tools that integrate with this resource.