MySQL Optimizer Hints & Histograms Prompt
Repair a bad MySQL 8.0 query plan using column histograms and optimizer hints instead of fighting the optimizer blind.
- Target user
- Engineers and DBAs tuning stubborn query plans on MySQL 8.0
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL 8.0 DBA who reads EXPLAIN and EXPLAIN ANALYZE output fluently and fixes bad query plans. You understand the cost-based optimizer, column histograms built with ANALYZE TABLE ... UPDATE HISTOGRAM, the difference between index hints (USE/FORCE/IGNORE INDEX) and optimizer hints (the /*+ ... */ comment syntax such as JOIN_ORDER, INDEX, NO_INDEX_MERGE, SET_VAR), and when histograms beat indexes on low-cardinality, skewed columns where an index would not help selectivity estimation. I will provide: - The slow query and its `EXPLAIN FORMAT=JSON` and `EXPLAIN ANALYZE` output: [PASTE] - `SHOW CREATE TABLE` for each table involved, including all indexes: [PASTE SCHEMA] - Approximate row counts and what I know about data skew in the filtered columns: [DESCRIBE] - Any existing histograms (`SELECT * FROM information_schema.column_statistics`) and the MySQL minor version: [PASTE] Work through this: 1. **Read the chosen plan.** Compare the optimizer's estimated rows (rows_examined_per_scan / rows_produced_per_join) against the actual rows from EXPLAIN ANALYZE. Pinpoint where the estimate diverges most — that misestimate is usually the root cause of the wrong join order or wrong access path. 2. **Decide histogram vs index.** If the bad estimate is on a low-cardinality or skewed column that does not benefit from an index (e.g. a status or flag column), recommend a histogram: `ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS`. If selectivity is high and access is the issue, recommend an index instead. Explain why one fits and the other does not. 3. **Choose the right hint type.** Distinguish index hints (USE/FORCE/IGNORE INDEX, which only constrain index choice for one table) from optimizer hints (the /*+ ... */ block, which can steer JOIN_ORDER, force/forbid a specific INDEX, disable INDEX_MERGE, or scope a session variable via SET_VAR). Prefer optimizer hints for join ordering and prefer refreshed statistics over hard-coded hints wherever possible. 4. **Propose the minimal change.** Give the exact ANALYZE TABLE statement and/or the rewritten query with the hint inline. Show the expected new plan and which estimate it corrects. 5. **State what to monitor.** Recommend re-checking the plan after the histogram is built, watching for plan drift after data changes, and noting that histograms are not auto-updated and must be refreshed after significant data movement. Output: (a) Plan diagnosis with the worst estimate divergence, (b) Histogram-vs-index decision and rationale, (c) Exact ANALYZE TABLE statement and/or hinted query, (d) Expected corrected plan, (e) Monitoring and refresh guidance. Guardrails: validate every histogram build and hinted query on a replica or staging copy first and back up before any schema or index change; treat hard-coded optimizer hints as a last resort because they pin the plan and can rot as data shifts; never deploy a hint or new index to production without reviewing the resulting plan under representative data.
Why this prompt works
Most engineers reach for an index or a FORCE INDEX hint the moment a query goes slow, but on MySQL 8.0 the more common root cause is a bad cardinality estimate. This prompt forces the model to do the one thing that actually diagnoses the problem: line up the optimizer’s estimated rows against the actual rows from EXPLAIN ANALYZE and find where they diverge. That single comparison tells you whether the optimizer chose a poor join order or a poor access path because it was working from wrong numbers, and it stops the guesswork that leads to a pile of useless indexes.
It also draws the distinction that trips people up: histograms versus indexes, and index hints versus optimizer hints. On a low-cardinality, skewed column — think a status field that is 95 percent one value — an index does little for selectivity, but a histogram gives the optimizer the distribution it needs to estimate correctly. And the /*+ … */ optimizer hints can steer join order or disable an index merge in ways that the old USE/FORCE/IGNORE INDEX syntax simply cannot. Naming the right tool for each situation is what separates a durable fix from a brittle one.
The guardrails reflect operational reality. Histograms in MySQL 8.0 are not refreshed automatically, so a fix that works today silently degrades as data shifts unless someone rebuilds them. Hard-coded hints pin a plan and rot the same way. By pushing the engineer toward refreshed statistics first and treating hints as a reviewed last resort, the prompt keeps the optimizer doing its job and the human owning the trade-off.