Histograms in MySQL and MariaDB
summary
MariaDB has had histograms since 10.0, stores compact histograms in mysql.column_stats (about 225 bytes) and collects them only via full table scan using ANALYZE TABLE (manual), with precision limits from bucket-count encoding. PostgreSQL keeps an MCV list plus a bucketed histogram and uses sampling plus autovacuum ANALYZE. MySQL 8.0 adds histogram scaffolding in mysql.column_stats (JSON) and a sampling API; its design estimates sample size as 300 × buckets = 30,000 rows for 100 buckets, and real-world tests show large errors when rare values sit near very common ones.
AI-written summary · about
chapters (12)
- 0:00Histograms in databases
- 1:04Query optimizers need histograms
- 4:16Estimating condition selectivity
- 6:43Histogram types and trade-offs
- 10:51Histogram collection strategies
- 12:19MariaDB histograms overview
- 18:34PostgreSQL histogram implementation
- 21:26MySQL sampling approach
- 25:47Single-column histogram limits
- 27:37Histogram accuracy test
- 33:49MySQL 8.0 histogram features
- 40:41Key conclusions
related talks
50mDiagnosing Database Issues With MySQL 8.0’s Performance Schema
35mMySQL Indexes and Histograms to Speed Up Your Queries
42mMariaDB and MySQL — What Statistics the Optimizer Uses
19mMariaDB Observability
30mMySQL Indexes and Histograms to Speed Up Your Queries
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries
17mState of MariaDB
27mAnalytical Queries in MySQL