Importing Existing Histogram Data in MySQL 8.0.31

MySQL 8.0 has supported histograms for years; version 8.0.31 adds a new syntax to import existing histogram data.

The new syntax is: ANALYZE TABLE table_name UPDATE HISTOGRAM ON column1[, columnN] USING DATA 'histogram_json'

Creating a histogram on a large table (e.g., 10 million rows) can take several seconds, as shown in the example:

<mysql:8.0.31:ytt>analyze table t1 update histogram on c1 with 1000 buckets;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| ytt.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+
1 row in set (5.34 sec)

The histogram metadata is stored in information_schema.column_statistics , where the HISTOGRAM column contains a JSON description of the buckets.

<mysql:8.0.31:ytt>select * from information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: ytt
TABLE_NAME: t1
COLUMN_NAME: c1
HISTOGRAM: {"buckets": [[1, 0.09946735110669537], [2, 0.20023182646133467], ...], "data-type": "int", "null-values": 0.0, ...}

Two typical scenarios require re‑importing histogram data: (1) frequent DML operations that change data distribution, and (2) accidental deletion of histogram metadata while the database is under heavy load.

By pre‑computing the histogram JSON (e.g., in a file histogram_new.txt ) and using the new import command, the operation completes in about 0.03 seconds, over 100× faster than building the histogram online.

[root@ytt-pc tmp]# mysql -uroot -p -D ytt -vv -e "analyze table t1 update histogram on c1 using data '`cat histogram_new.txt`'";
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| ytt.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+
1 row in set (0.03 sec)

This feature helps reduce database load and speeds up statistics updates for large tables.