mysql.column_stats Table

The mysql.column_stats table stores engine-independent column statistics, such as histograms, used by the optimizer to improve query execution plans.

The mysql.column_stats table is one of three tables storing data used for Engine-independent table statistics. The others are mysql.table_stats and mysql.index_stats.

It is populated when the ANALYZE TABLE statement is run, although not by default. See Collecting Statistics with the ANALYZE TABLE Statement for details.

Note that statistics for blob and text columns are not collected. If explicitly specified, a warning is returned.

It is possible to manually update the table and, unlike most system tables, there are some scenarios where this could be useful. See Manual updates to statistics tables for details.

This table uses the Aria storage engine.

The mysql.column_stats table contains the following fields:

Field	Type	Null	Key	Default	Description
db_name	varchar(64)	NO	PRI	NULL	Database the table is in.
table_name	varchar(64)	NO	PRI	NULL	Table name.
column_name	varchar(64)	NO	PRI	NULL	Name of the column.
min_value	varbinary(255)	YES		NULL
max_value	varbinary(255)	YES		NULL
nulls_ratio	decimal(12,4)	YES		NULL	Fraction of NULL values (0- no NULLs, 0.5 - half values are NULLs, 1 - all values are NULLs).
avg_length	decimal(12,4)	YES		NULL	Average length of column value, in bytes. Counted as if one ran SELECT AVG(LENGTH(col)). This doesn't count NULL bytes, assumes endspace removal for CHAR(n), etc.
avg_frequency	decimal(12,4)	YES		NULL	Average number of records with the same value
hist_size	tinyint(3) unsigned	YES		NULL	Histogram size in bytes, from 0-255, or, from MariaDB 10.8, number of buckets if the histogram type is JSON_HB.
hist_type	enum('SINGLE_PREC_HB', 'DOUBLE_PREC_HB') (from MariaDB 10.8) enum('SINGLE_PREC_HB', 'DOUBLE_PREC_HB','JSON_HB') (until MariaDB 10.7)	YES		NULL	Histogram type. See the histogram_type system variable.
histogram	blob (from MariaDB 10.7) varbinary(255) (until MariaDB 10.7)	YES		NULL

_{This page is licensed: CC BY-SA / Gnu FDL}