DBAtlas · Database operations atlas

Create and manage histograms for non-indexed columns

MySQLIndex & Statistics

Command

ANALYZE TABLE shop.big UPDATE HISTOGRAM ON status WITH 64 BUCKETS;
-- 8.4+: histogram refreshed automatically as data changes
ANALYZE TABLE shop.big UPDATE HISTOGRAM ON status WITH 64 BUCKETS AUTO UPDATE;

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       HISTOGRAM->>'$."histogram-type"' AS type,
       JSON_LENGTH(HISTOGRAM->'$.buckets') AS buckets,
       HISTOGRAM->>'$."last-updated"' AS last_updated,
       HISTOGRAM->>'$."sampling-rate"' AS sampling_rate
FROM information_schema.COLUMN_STATISTICS;

ANALYZE TABLE shop.big DROP HISTOGRAM ON status;

What it does

Stores the value distribution of a column (singleton or equi-height) in the data dictionary; the optimizer estimates the selectivity of filters on non-indexed columns correctly, improving join order and plan quality.

When to use it

When EXPLAIN ANALYZE shows a large estimated vs. actual row gap on skewed columns (e.g. status, type) where adding an index is not desirable.

Caution

On indexed columns the optimizer uses index dives, so a histogram usually has no effect (unless eq_range_index_dive_limit is exceeded). In the default MANUAL mode the histogram is not refreshed as data changes; schedule ANALYZE ... UPDATE HISTOGRAM. Large tables are sampled according to histogram_generation_max_mem_size (sampling-rate < 1). ANALYZE TABLE takes a brief metadata lock; updating a histogram does not change table statistics (index cardinality).

Open in DBAtlas →

Related