# TopN + TopN counts the values of a column or index that appear most frequently + By default, the top 20 most frequent values for each column/index are collected (N = 20) + By default, 500 TopN values are collected. ``` tidb> SHOW ~!@STATS_TOPN@!~ WHERE db_name = 'test' AND table_name = 'T1'; +---------+------------+----------------+-------------+----------+----------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+----------+-------+ | test | T1 | | id | 0 | 1 | 1 | | test | T1 | | id | 0 | 2 | 1 | | test | T1 | | id | 0 | 3 | 1 | | test | T1 | | id | 0 | 4 | 1 | | test | T1 | | id | 0 | 5 | 1 | | test | T1 | | id | 0 | 6 | 1 | | test | T1 | | id | 0 | 7 | 1 | | test | T1 | | id | 0 | 8 | 1 | | test | T1 | | id | 0 | 9 | 1 | | test | T1 | | id | 0 | 10 | 1 | | test | T1 | | name | 0 | Andy | 1 | ... | test | T1 | | idx_name | 1 | Tony | 1 | | test | T1 | | idx_name | 1 | Tracy | 1 | | test | T1 | | idx_city | 1 | Seattle | 5 | | test | T1 | | idx_city | 1 | Shanghai | 5 | +---------+------------+----------------+-------------+----------+----------+-------+ 42 rows in set (0.00 sec) ``` ------------------------------------------------------------------------------ `TopN` statistics are highly beneficial as they track the most frequently occurring values within a column or index, along with their corresponding occurrence counts. When the system variable `tidb_analyze_version` is set to 2, 500 `TopN` values are collected by default. The following example illustrates the collection of `TopN`: ``` 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.01 sec) ERROR: No query specified 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> ANALYZE TABLE T1; Query OK, 0 rows affected, 1 warning (0.18 sec) tidb> SHOW STATS_TOPN WHERE db_name = 'test' AND table_name = 'T1'; +---------+------------+----------------+-------------+----------+----------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+----------+-------+ | test | T1 | | id | 0 | 1 | 1 | | test | T1 | | id | 0 | 2 | 1 | | test | T1 | | id | 0 | 3 | 1 | | test | T1 | | id | 0 | 4 | 1 | | test | T1 | | id | 0 | 5 | 1 | | test | T1 | | id | 0 | 6 | 1 | | test | T1 | | id | 0 | 7 | 1 | | test | T1 | | id | 0 | 8 | 1 | | test | T1 | | id | 0 | 9 | 1 | | test | T1 | | id | 0 | 10 | 1 | | test | T1 | | name | 0 | Andy | 1 | | test | T1 | | name | 0 | Candy | 1 | | test | T1 | | name | 0 | Cici | 1 | | test | T1 | | name | 0 | Frank | 1 | | test | T1 | | name | 0 | Jack | 1 | | test | T1 | | name | 0 | Mark | 1 | | test | T1 | | name | 0 | Tom | 2 | | test | T1 | | name | 0 | Tony | 1 | | test | T1 | | name | 0 | Tracy | 1 | | test | T1 | | city | 0 | Seattle | 5 | | test | T1 | | city | 0 | Shanghai | 5 | | test | T1 | | age | 0 | 22 | 1 | | test | T1 | | age | 0 | 23 | 1 | | test | T1 | | age | 0 | 24 | 1 | | test | T1 | | age | 0 | 25 | 1 | | test | T1 | | age | 0 | 28 | 1 | | test | T1 | | age | 0 | 30 | 1 | | test | T1 | | age | 0 | 32 | 1 | | test | T1 | | age | 0 | 34 | 1 | | test | T1 | | age | 0 | 38 | 1 | | test | T1 | | age | 0 | 41 | 1 | | test | T1 | | idx_name | 1 | Andy | 1 | | test | T1 | | idx_name | 1 | Candy | 1 | | test | T1 | | idx_name | 1 | Cici | 1 | | test | T1 | | idx_name | 1 | Frank | 1 | | test | T1 | | idx_name | 1 | Jack | 1 | | test | T1 | | idx_name | 1 | Mark | 1 | | test | T1 | | idx_name | 1 | Tom | 2 | | test | T1 | | idx_name | 1 | Tony | 1 | | test | T1 | | idx_name | 1 | Tracy | 1 | | test | T1 | | idx_city | 1 | Seattle | 5 | | test | T1 | | idx_city | 1 | Shanghai | 5 | +---------+------------+----------------+-------------+----------+----------+-------+ 42 rows in set (0.00 sec) ``` Table T1 is a Clustered Index table featuring the `id` column as its primary key. In addition to this, there are two indexes: `idx_name` on the `name` column and `idx_city` on the `city` column. The `idx_city` index shows poor selectivity with only two distinct values present in the `city` column. The table consists of 10 rows of data. To view the `TopN` statistics for table T1, the command `SHOW STATS_TOPN WHERE db_name = 'test' AND table_name = 'T1';` is utilized. This reveals that each column and index in the table is accounted for in the statistics. For instance, in the `name` column and `idx_name` index, the occurrence count for each value (`Value` item) is recorded, with the name `Tom` appearing twice. Similarly, in the `city` column and `idx_city` index, the occurrence count for each value (`Value` item) is collected, showing only two values, `Seattle` and `Shanghai`, each appearing five times. Although more statistics information is collected than there are rows in the table, it is unlikely that this would prevent the optimizer from generating an optimal execution plan. However, collecting detailed statistics for every table could consume a substantial amount of space. Therefore, in practice, and when `tidb_analyze_version` is set to 2 (the default starting from TiDB v5.3), a maximum of 500 `TopN` value records are collected by default, focusing on the queries currently in operation.