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 |
+----------+------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+