DECODE_HISTOGRAM

Syntax

DECODE_HISTOGRAM(histogram_type,histogram_string)

Note: Before MariaDB-10.0.10 the arguments where reversed.

Description

Returns a string of comma separated numeric values corresponding to a probability distribution represented by the histogram_string of type histogram_type (SINGLE_PREC_HB or DOUBLE_PREC_HB). The histogram_type corresponds the. Usually for pulling directly from the mysql.column_stats table.

See histogram-based-statistics for details.

Examples

MariaDB [test]> CREATE TABLE origin (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, v INT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO origin(v) VALUES (1),(2),(3),(4),(5),(10),(20),(30),(40),(50),(60),(70),(80),(90),(100),(200),(400),(800);
Query OK, 18 rows affected (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 0

MariaDB [test]> 
MariaDB [test]> set histogram_size=10,histogram_type=SINGLE_PREC_HB;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> 
MariaDB [test]> ANALYZE TABLE origin PERSISTENT FOR ALL;
+-------------+---------+----------+-----------------------------------------+
| Table       | Op      | Msg_type | Msg_text                                |
+-------------+---------+----------+-----------------------------------------+
| test.origin | analyze | status   | Engine-independent statistics collected |
| test.origin | analyze | status   | OK                                      |
+-------------+---------+----------+-----------------------------------------+
2 rows in set (0.01 sec)

MariaDB [test]> select db_name,table_name,column_name,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where db_name='test' and table_name='origin';
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
| db_name | table_name | column_name | hist_type      | hex(histogram)       | decode_histogram(hist_type,histogram)                             |
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
| test    | origin     | i           | SINGLE_PREC_HB | 0F2D3C5A7887A5C3D2F0 | 0.059,0.118,0.059,0.118,0.118,0.059,0.118,0.118,0.059,0.118,0.059 |
| test    | origin     | v           | SINGLE_PREC_HB | 000001060C0F161C1F7F | 0.000,0.000,0.004,0.020,0.024,0.012,0.027,0.024,0.012,0.376,0.502 |
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)