📊 Fixing MySQL Query Plans With Histograms and Optimizer Hints, With AI

I’ve spent more late nights than I’d like to admit staring at a query that ran fine for months and then suddenly decided to do a full table scan against a hundred-million-row table. The data didn’t change shape overnight, but the optimizer’s view of it did. MySQL’s cost-based optimizer makes its decisions from statistics, and when those statistics lie to it, it confidently picks a terrible plan. Over the last year my workflow for these incidents has shifted: I now lean on AI to read the EXPLAIN ANALYZE output and explain why the optimizer chose what it chose, but I never apply a fix in production until I’ve reproduced it on a replica. AI drafts, the replica verifies.

When the Optimizer Picks a Bad Plan

The optimizer’s job is to estimate how many rows each access path will touch and pick the cheapest combination. The trouble is that by default MySQL only knows index cardinality (the number of distinct values), not how values are distributed. For a column like status where 98% of rows are 'active' and 2% are spread across five other states, cardinality says “six distinct values” and the optimizer happily assumes each value matches roughly one-sixth of the table. So a query filtering on status = 'cancelled' gets estimated at one-sixth of the rows when it’s really 0.4%, and the optimizer skips a perfectly good index in favor of a scan.

You see this most often on skewed, low-cardinality columns: status flags, country codes, tenant IDs in a noisy-neighbor table, soft-delete booleans. Those are exactly the columns that don’t get a great index but desperately need accurate selectivity estimates.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query and reports estimated versus real rows, which is the single most useful signal you have. The gap between estimate and actual is where bad plans hide.

EXPLAIN ANALYZE
SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.status = 'cancelled'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

When the output shows something like (cost=... rows=2.1M) (actual ... rows=8200 ...), that three-orders-of-magnitude gap between rows=2.1M estimated and rows=8200 actual is the smoking gun. The optimizer thought status = 'cancelled' was common, so it never considered the index on status.

This is where I hand the raw output to AI. I paste the full EXPLAIN ANALYZE tree and ask it to walk me through the discrepancy.

You are a senior MySQL 8.0 DBA. Here is an EXPLAIN ANALYZE tree for a slow query. Identify where the optimizer’s estimated rows diverge most from actual rows, explain the most likely statistical cause, and propose two fixes: one using a histogram and one using an optimizer hint. Do not assume table sizes I haven’t given you.

The reply is genuinely useful as a starting hypothesis, not as gospel:

The largest divergence is at the status = 'cancelled' predicate: estimated 2.1M rows vs 8,200 actual, a ~256x overestimate. With no histogram on status, the optimizer assumes uniform distribution across distinct values, so a rare value like ‘cancelled’ is badly overestimated. This pushed it toward a table scan instead of ref access on the status index. Fix 1: build a histogram on status so the optimizer knows ‘cancelled’ is rare. Fix 2: force the index with /*+ INDEX(o idx_status_created) */. Verify the histogram fix first since it helps all queries, not just this one.

That’s a hypothesis I can test, and the histogram-first advice matches how I’d reason about it. But I still verify.

Building Histograms on Skewed Columns

Histograms give the optimizer a value-distribution map without the maintenance cost of an index. You build them with ANALYZE TABLE ... UPDATE HISTOGRAM, and they’re stored in the data dictionary, not refreshed automatically, so they’re a deliberate choice.

-- Build a histogram with a sensible bucket count for a skewed column
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status, country_code WITH 64 BUCKETS;

-- Inspect what MySQL recorded
SELECT column_name, json_pretty(histogram)
FROM information_schema.column_statistics
WHERE schema_name = 'shop' AND table_name = 'orders';

For a low-cardinality column like status with six distinct values, MySQL builds a singleton histogram (one bucket per value) and the bucket count barely matters. For higher-cardinality skewed columns it builds an equi-height histogram, and 64 to 100 buckets is a reasonable default. Don’t put histograms on columns that already have a selective index and uniform distribution; they add nothing there and cost you a manual refresh whenever the distribution shifts.

To drop one:

ANALYZE TABLE orders DROP HISTOGRAM ON status;

The critical operational detail: histograms do not update when the data changes. If your status mix shifts seasonally, schedule a periodic UPDATE HISTOGRAM. I run mine from a nightly maintenance job and let the rebuild timer pick up the change.

Optimizer Hints Versus Old-Style Index Hints

When statistics alone can’t steer the plan, hints are the next lever. MySQL 8.0 has two styles, and I strongly prefer the newer one.

Old-style index hints live after the table name and are blunt:

SELECT * FROM orders USE INDEX (idx_status_created)
WHERE status = 'cancelled';

The modern optimizer hints live in a /*+ ... */ comment right after SELECT and are far more expressive. They let you pin join order and index choice per table without rewriting the query structure:

SELECT /*+ JOIN_ORDER(o, c) INDEX(o idx_status_created) */
       o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'cancelled'
  AND o.created_at >= '2026-01-01';

JOIN_ORDER(o, c) tells the optimizer to drive from orders first, which is correct when the status filter is highly selective. INDEX(o idx_status_created) restricts orders access to that index. Because these hints name tables explicitly, they survive refactors better than positional USE INDEX, and you can combine JOIN_ORDER, INDEX, NO_INDEX, and BNL/NO_BNL for block-nested-loop control in one comment block.

My rule: hints are a tourniquet, not a cure. A histogram fixes the estimate for every query touching that column; a hint fixes exactly one query and freezes the plan against future data changes. Reach for the histogram first, fall back to a hint only when you must ship a fix this minute.

Verifying on a Replica

This is the part I won’t skip. AI’s explanation is a hypothesis built from text it can’t execute. Before any change reaches production, I reproduce it against a read replica that carries the real data distribution.

# Run the proposed fix against a replica, capturing the plan
mysql -h replica-01.internal -u dba -p shop <<'SQL'
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 64 BUCKETS;
EXPLAIN ANALYZE
SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.status = 'cancelled'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
SQL

I compare the before and after trees side by side. If the estimated rows now sit close to actual, the access path flipped to ref, and wall-clock time dropped, the fix is real. If the estimate barely moved, the histogram wasn’t the problem and I dig further rather than trusting the AI’s first guess. Building a histogram on a replica is safe and isolated, which makes it the perfect proving ground; the worst case is a slightly stale statistic you can drop.

Once the replica confirms it, I promote the change deliberately: histograms via the maintenance job, hints via a reviewed query change with a comment explaining why the plan needed pinning.

The Workflow That Stuck

The pattern that’s served me well is simple. Capture EXPLAIN ANALYZE, let AI find the estimate-versus-actual gap and explain the statistical cause, prefer a histogram over a hint, and verify everything on a replica before it touches production. AI compresses the diagnosis from twenty minutes of plan-reading to two, but the replica is what keeps me honest.

If you want more of these query-tuning playbooks, browse the MySQL category, and for the exact wording I use when handing plans to an assistant, see my collection of AI prompts. The combination of accurate statistics, surgical hints, and a replica you trust will get you out of almost any bad-plan incident.