# Column Metadata and Statistics

```
tidb> SHOW ~!@STATS_HISTOGRAMS@!~ WHERE db_name='test' AND table_name='T1'\G;
...
*************************** 5. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: idx_name
 Is_index: 1
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 9
 Null_count: 0
 Avg_col_size: 0
 Correlation: 0
 Load_status: allLoaded
Total_mem_usage: 410
 Hist_mem_usage: 0
 Topn_mem_usage: 410
 Cms_mem_usage: 0
...
```
------------------------------------------------------------------------------
The term "basic column information," also referred to as column metadata, encompasses details such as the number of unique values in a column and the count of NULL values, among other data points. This information can be accessed using the `SHOW STATS_HISTOGRAMS` command. For example:

```
tidb> SHOW CREATE TABLE T1\G
*************************** 1. row ***************************
 Table: T1
Create Table: CREATE TABLE `T1` (
 `id` int(11) NOT NULL,
 `name` varchar(32) DEFAULT NULL,
 `city` varchar(16) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
 KEY `idx_name` (`name`),
 KEY `idx_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

tidb> SELECT * FROM T1;
+----+-------+----------+------+
| id | name  | city     | age  |
+----+-------+----------+------+
| 1  |  Tom  | Seattle  |  34  |
| 2  | Jack  | Seattle  |  32  |
| 3  | Frank | Seattle  |  24  |
| 4  | Tony  | Seattle  |  30  |
| 5  | Mark  | Seattle  |  28  |
| 6  |  Tom  | Shanghai |  41  |
| 7  | Candy | Shanghai |  25  |
| 8  | Andy  | Shanghai |  23  |
| 9  | Cici  | Shanghai |  22  |
| 10 | Tracy | Shanghai |  38  |
+----+-------+----------+------+
10 rows in set (0.00 sec)

tidb> SHOW STATS_HISTOGRAMS WHERE db_name='test' AND table_name='T1'\G
*************************** 1. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: id
 Is_index: 0
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 10
 Null_count: 0
 Avg_col_size: 8
 Correlation: 1
 Load_status: allLoaded
Total_mem_usage: 442
 Hist_mem_usage: 0
 Topn_mem_usage: 442
 Cms_mem_usage: 0
*************************** 2. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: name
 Is_index: 0
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 9
 Null_count: 0
 Avg_col_size: 5.1
 Correlation: -0.12727272727272726
 Load_status: allLoaded
Total_mem_usage: 410
 Hist_mem_usage: 0
 Topn_mem_usage: 410
 Cms_mem_usage: 0
*************************** 3. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: city
 Is_index: 0
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 2
 Null_count: 0
 Avg_col_size: 8.5
 Correlation: 1
 Load_status: allLoaded
Total_mem_usage: 125
 Hist_mem_usage: 0
 Topn_mem_usage: 125
 Cms_mem_usage: 0
*************************** 4. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: age
 Is_index: 0
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 10
 Null_count: 0
 Avg_col_size: 8
 Correlation: -0.23636363636363636
 Load_status: allLoaded
Total_mem_usage: 442
 Hist_mem_usage: 0
 Topn_mem_usage: 442
 Cms_mem_usage: 0
*************************** 5. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: idx_name
 Is_index: 1
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 9
 Null_count: 0
 Avg_col_size: 0
 Correlation: 0
 Load_status: allLoaded
Total_mem_usage: 410
 Hist_mem_usage: 0
 Topn_mem_usage: 410
 Cms_mem_usage: 0
*************************** 6. row ***************************
 Db_name: test
 Table_name: T1
 Partition_name:
 Column_name: idx_city
 Is_index: 1
 Update_time: 2023-08-18 12:50:43
 Distinct_count: 2
 Null_count: 0
 Avg_col_size: 0
 Correlation: 0
 Load_status: allLoaded
Total_mem_usage: 125
 Hist_mem_usage: 0
 Topn_mem_usage: 125
 Cms_mem_usage: 0
6 rows in set (0.00 sec)
```

Consider table T1, which is a Clustered Index table with the `id` column serving as the primary key. In addition, there are two indexes: one on the `name` column (KEY `idx_name`) and another on the `city` column (KEY `idx_city`). The index on the `city` column exhibits poor selectivity, as it contains only two distinct values. The table itself comprises 10 rows of data.

By executing the command `SHOW STATS_HISTOGRAMS WHERE db_name='test' AND table_name='T1'\G`, one can retrieve the basic column information. For instance, regarding the `name` column and its associated index `idx_name`, the number of distinct values is calculated to be 9 (`Distinct_count`), with NULL values numbering 0 (`Null_count`). Similarly, for the `city` column and its index `idx_city`, there are 2 distinct values (`Distinct_count`) and 0 NULL values (`Null_count`). This metadata is helpful for making informed index selections.

For comprehensive details on the `SHOW STATS_HISTOGRAMS` command, please refer to the official documentation: [TiDB Statistics Metadata](https://docs.pingcap.com/tidb/stable/statistics#metadata-of-columns).