Analyze the Statistics for table, columns & partitions histogram in Mysql

Check the Statistics in the MySQL database

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name';

Generate the Statistics for the table in the MySQL database

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name]..
Example:
mysql> ANALYZE TABLE city;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| world.city | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.07 sec)

Note: Read last note with example for ANALYZE table make it Uninitialized and run stats on next table access.

Generate stats for a table with Histogram of columns

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] ..[WITH N BUCKETS]

Example:
mysql> ANALYZE TABLE city UPDATE HISTOGRAM ON Name,District WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: world.city
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'District'.
*************************** 2. row ***************************
   Table: world.city
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'Name'.
2 rows in set (0.17 sec)

Remove histogram statistics for column in table

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name] ..

Example:
mysql> ANALYZE TABLE city DROP HISTOGRAM ON District;
+------------+-----------+----------+-----------------------------------------------------+
| Table      | Op        | Msg_type | Msg_text                                            |
+------------+-----------+----------+-----------------------------------------------------+
| world.city | histogram | status   | Histogram statistics removed for column 'District'. |
+------------+-----------+----------+-----------------------------------------------------+
1 row in set (0.01 sec)

Gather statistics for Partition in a table

ALTER TABLE table_name ANALYZE PARTITION {partition_names | ALL};

Check information from the following views:

-- Provide information about table indexes
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name'
  
-- Information about column statistics
select * from INFORMATION_SCHEMA.COLUMN_STATISTICS where schema_name = 'db_name' and table_name = 'tbl_name';

SELECT HISTOGRAM FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE TABLE_NAME = 'city' AND COLUMN_NAME = 'name';

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where name ='world/city';

Note:
ANALYZE TABLE clears table statistics from the INFORMATION_SCHEMA.INNODB_TABLESTATS table and
sets the STATS_INITIALIZED column to Uninitialized.
Statistics are collected again the next time the table is accessed.

Example of Note:

mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where name ='world/city';
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME       | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|     1088 | world/city | Initialized       |     4188 |               25 |                8 |                0 |    4080 |         2 |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql> analyze table city;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| world.city | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where name ='world/city';
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME       | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|     1088 | world/city | Uninitialized     |        0 |                0 |                0 |                0 |    4080 |         1 |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where name ='world/city';
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME       | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|     1088 | world/city | Initialized       |     4188 |               25 |                8 |                0 |    4080 |         2 |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+