In MySQL 8.4, the optimizer misjudges a skewed, non-indexed column and picks a bad join order; when do you add a histogram, and when an optimizer hint?
answer
- fix the estimate or pin the plan
- ANALYZE TABLE builds it on demand
- singleton or equi-height buckets
- hints live inside the statement
basics
~20 sAdd a histogram (ANALYZE TABLE ... UPDATE HISTOGRAM) when a non-indexed column's row estimate is wrong; it fixes every query filtering on it. Use a /*+ ... */ optimizer hint only to pin one statement's plan that better statistics cannot fix.
solid answer
~50 sA histogram fixes the cause. `ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS` stores the column's value distribution in the data dictionary, so the optimizer can estimate how many rows `col = constant` or a range keeps, which helps most on non-indexed columns. When an index range estimate is available, the optimizer prefers it and ignores the histogram. A histogram adds no write overhead but goes stale; in MySQL 8.4 `AUTO UPDATE` lets `ANALYZE TABLE` and InnoDB's automatic statistics recalculation refresh it. A hint such as `/*+ JOIN_ORDER(c, s) */` or `/*+ INDEX(s idx) */` pins one statement's plan. It needs a code change, stays pinned as the data shifts, and is silently ignored when it cannot be applied. So I reach for the histogram first and keep hints for plans the statistics cannot fix.
code
sql · 8 linesANALYZE TABLE shipments
UPDATE HISTOGRAM ON carrier_code WITH 64 BUCKETS AUTO UPDATE;
SELECT COLUMN_NAME,
HISTOGRAM->>'$."histogram-type"' AS type,
HISTOGRAM->>'$."last-updated"' AS last_updated
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'shipments';go deeper
Recall that ANALYZE TABLE ... UPDATE HISTOGRAM builds column statistics and that optimizer hints go in a /*+ */ comment after the first keyword.
Explain singleton versus equi-height buckets, why histograms matter for non-indexed columns, and which join-order and index-level hints exist.
Show that you fix estimates before pinning plans, verify with EXPLAIN ANALYZE and SHOW WARNINGS, and plan histogram refresh with AUTO UPDATE or scheduled ANALYZE TABLE.
Set a policy for where hints may live, who reviews them and when they expire, and which skewed columns get histograms as part of schema ownership.
## The problem both tools address MySQL's cost-based optimizer chooses join order and access paths from **row estimates**. For an indexed column it can measure selectivity directly, using index dives or stored index statistics. For a **non-indexed** column it has no distribution data, so a predicate like `carrier_code = 'X9'` gets a generic guess. With skewed data, where one value covers 70 percent of the rows and another covers 0.01 percent, that guess can be wrong by orders of magnitude and the join order built on it collapses. Statistics theory in general is covered with optimizer statistics. This question is about MySQL's two levers. ## Histograms: fixing the estimate A **histogram** records how a column's values are distributed. MySQL builds one only when asked: - **Create or refresh:** `ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2 WITH 64 BUCKETS;` The bucket count ranges from 1 to 1024 and defaults to 100. Only one table is allowed per statement. - **Type, chosen automatically:** a **singleton** histogram (one bucket per value) when the number of distinct values is at most the bucket count, otherwise **equi-height** (each bucket covers a range). - **Storage:** the data dictionary, readable through `INFORMATION_SCHEMA.COLUMN_STATISTICS` as JSON, including `last-updated` and `sampling-rate`. Building one may read only a sample of the rows, and `histogram_generation_max_mem_size` caps the memory it may use. - **Drop:** `ANALYZE TABLE t DROP HISTOGRAM ON c1;` - **Side effects:** `ANALYZE TABLE` takes a read lock on the table while it runs, and it is written to the binary log, so it replicates unless you add `NO_WRITE_TO_BINLOG` (or `LOCAL`). - **Not supported:** geometry (spatial) and JSON columns. How the optimizer uses it: - It uses histograms for comparisons with constants: `=`, `<>`, `<`, `>`, `BETWEEN`, `IN`, `IS NULL` and their negations. - It **prefers range-optimizer estimates**. If the range optimizer applies, as it usually does on an indexed column compared with constants, the histogram is not used. Histograms are therefore most useful on **non-indexed** columns. - The effect is visible in `EXPLAIN` as a more realistic `filtered` percentage. Maintenance is the trade-off. A histogram costs nothing on `INSERT` or `UPDATE`, unlike an index, but it does not change as the data does. **New in MySQL 8.4.0**, `AUTO UPDATE` changes that: | Option | Behaviour | |---|---| | `MANUAL UPDATE` (default) | Stays as built until you run `UPDATE HISTOGRAM` again | | `AUTO UPDATE` | Refreshed by every plain `ANALYZE TABLE` on the table and by InnoDB's automatic persistent-statistics recalculation, with the same bucket count | ## Optimizer hints: pinning the plan An **optimizer hint** is a `/*+ ... */` comment directly after `SELECT`, `INSERT`, `REPLACE`, `UPDATE` or `DELETE` (or at the start of a query block). Hints override `optimizer_switch` for that one statement. Useful ones here: - **Join order:** `JOIN_ORDER(t1, t2)`, `JOIN_PREFIX`, `JOIN_SUFFIX`, `JOIN_FIXED_ORDER`. - **Index choice:** `INDEX`, `NO_INDEX`, `JOIN_INDEX`, `ORDER_INDEX`, `GROUP_INDEX`. In 8.4 they are intended to supersede the `FORCE INDEX` and `IGNORE INDEX` index hints, and the manual expects `USE INDEX`, `FORCE INDEX` and `IGNORE INDEX` to be deprecated in a future release. - **Guards:** `MAX_EXECUTION_TIME(N)` caps a read-only `SELECT` at N milliseconds. `SET_VAR(var = value)` changes one session variable for the statement only, provided the variable allows it. Some behaviours surprise people: 1. A valid hint that cannot be applied, for example a join order a `LEFT JOIN` forbids, is **silently ignored with no warning**. `SHOW WARNINGS` after `EXPLAIN` lists the hints actually used. 2. With conflicting or duplicate hints the first one wins. 3. `HASH_JOIN` and `NO_HASH_JOIN` **have no effect in MySQL 8.4**. Hash joins are controlled through `BNL` and `NO_BNL`. ## Choosing between them | | Histogram | Hint | |---|---|---| | Fixes | The estimate for every query on the column | One statement's plan | | Change needed | One `ANALYZE TABLE` by a DBA | An application code change and a deploy | | As data shifts | Re-estimates after a refresh | Stays pinned, right or wrong | | Failure mode | Stale buckets | Silently ignored, or a plan that no longer fits | Start with the histogram because it fixes the cause, then verify with `EXPLAIN ANALYZE`. Use a hint when the estimate is already right and the optimizer still picks badly, or as a short-term fix while a schema change is prepared. Write down why each hint exists so someone can remove it later.
- You built a histogram on an indexed MySQL column and the plan did not change. Why?The optimizer prefers range-optimizer estimates, such as index dives, to histogram statistics, and does not use the histogram when the range optimizer applies. For an indexed column filtered against constants, the index already supplies the estimate. Histograms pay off mainly on non-indexed columns.
- Why is a MySQL optimizer hint risky as a permanent fix?It freezes one choice while the data keeps changing, it lives in application code that DBAs may not see, and a valid hint that becomes impossible to apply is silently ignored with no warning. Prefer fixing the estimate, and record why any remaining hint exists.
- MySQL updates a histogram on every INSERT and UPDATE to the table.
- A histogram is a cheap index the optimizer can use to look rows up.
- Adding /*+ HASH_JOIN(t1) */ forces a hash join in MySQL 8.4.
- A hint that cannot be applied makes MySQL raise an error, so you notice.
- Histograms help most on columns that already have an index.
Assumes MySQL 8.4; AUTO UPDATE histograms are new in 8.4.0, HASH_JOIN hints have no effect in 8.4, and index-level hints are intended to supersede FORCE INDEX.