MySQL provides commands to see the overall index size versus the data size.<\/p>\n
One of them is “show table status” :<\/p>\n
mysql> show table status like 't'\\G\r\n*************************** 1. row ***************************\r\n Name: t\r\n Engine: InnoDB\r\n Version: 10\r\n Row_format: Compact\r\n Rows: 4186170\r\n Avg_row_length: 34\r\n Data_length: 143310848\r\nMax_data_length: 0\r\n Index_length: 146030592\r\n Data_free: 6291456\r\n Auto_increment: NULL\r\n Create_time: 2014-02-04 15:40:54\r\n Update_time: NULL\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options:\r\n Comment:\r\n1 row in set (0.00 sec)<\/pre>\nSo here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates :<\/p>\n
Data_length: 143310848, \u00a0\u00a0 136Mb\u00a0 clustered index size.<\/p>\n
Index_length: 146030592,\u00a0 139Mb secondary index size.<\/p>\n
In this example, I have 3 indexes : 1 auto-generated clustered index and 2 secondary indexes.<\/p>\n
CREATE TABLE `t` (\r\n `a` smallint(6) DEFAULT NULL,\r\n `b` smallint(6) DEFAULT NULL,\r\n `c` smallint(6) DEFAULT NULL,\r\n KEY `a` (`a`),\r\n KEY `b` (`b`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1<\/pre>\nIn this case, having the index_length, it is easy to guess the index size of each secondary index. In the general case, it is possible to drop secondary indexes one by one, optimize and see the change in index_length …<\/p>\n
From 5.6, there is a better way : this is the hidden 5.6 gem I want to share with you :<\/p>\n
ANALYZE table t;\r\nSELECT\r\n sum(stat_value) pages,\r\n index_name,\r\n sum(stat_value) * @@innodb_page_size size\r\nFROM\r\n mysql.innodb_index_stats\r\nWHERE\r\n table_name = 't'\r\n AND database_name = 'test'\r\n AND stat_description = 'Number of pages in the index'\r\nGROUP BY\r\n index_name;\r\n\r\n+-------+-----------------+-----------+\r\n| pages | index_name\u00a0\u00a0\u00a0\u00a0\u00a0 | size\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------+-----------------+-----------+\r\n|\u00a0 8747 | GEN_CLUST_INDEX | 143310848 |\r\n|\u00a0 4456 | a\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 73007104 |\r\n|\u00a0 4457 | b\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 73023488 |\r\n+-------+-----------------+-----------+\r\n3 rows in set (0.00 sec)<\/pre>\nUsing default options, MySQL 5.6 now computes table and index statistics “on the fly” and persist them in these 2 tables : mysql.innodb_table_stats and mysql.innodb_index_stats.<\/p>\n
If you need exact values, run ANALYZE TABLE<\/strong> before running the query ! The stats could be out of date due to a (dynamic) configuration change or if the table is just being heavily updated.<\/p>\n
Regarding index stats, each index has a stat called ‘Number of pages in the index<\/strong>‘. The index size can be obtained by multiplying this value by the InnoDB page size.<\/p>\n
More details on index stats can be found in this MySQL optimizer blog by my colleague Oystein : http:\/\/oysteing.blogspot.co.uk\/2011\/05\/innodb-persistent-statistics-save-day.html.<\/a>\u00a0 The MySQL manual should also soon be updated with this useful information.<\/p>\n
Note that the tip also works with partitioned table :<\/p>\n
mysql> alter table t partition by key(c) partitions 4;\r\nQuery OK, 4194308 rows affected (44.03 sec)\r\nRecords: 4194308 Duplicates: 0 Warnings: 0\r\n\r\nmysql> show create table t\\G\r\n*************************** 1. row ***************************\r\n Table: t\r\nCreate Table: CREATE TABLE `t` (\r\n `a` smallint(6) DEFAULT NULL,\r\n `b` smallint(6) DEFAULT NULL,\r\n `c` smallint(6) DEFAULT NULL,\r\n KEY `a` (`a`),\r\n KEY `b` (`b`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n\/*!50100 PARTITION BY KEY (c)\r\nPARTITIONS 4 *\/\r\n1 row in set (0.01 sec)\r\n\r\nANALYZE TABLE t;\r\n\r\nSELECT\r\n sum(stat_value) pages,\r\n index_name,\r\n sum(stat_value) * @@innodb_page_size size\r\nFROM\r\n mysql.innodb_index_stats\r\nWHERE\r\n table_name LIKE 't#P%'\r\n AND database_name = 'test'\r\n AND stat_description LIKE 'Number of pages in the index'\r\nGROUP BY\r\n index_name;\r\n\r\n+-------+-----------------+-----------+\r\n| pages | index_name | size |\r\n+-------+-----------------+-----------+\r\n| 8848 | GEN_CLUST_INDEX | 144965632 |\r\n| 5004 | a | 81985536 |\r\n| 5004 | b | 81985536 |\r\n+-------+-----------------+-----------+\r\n3 rows in set (0.00 sec)\r\n\r\nmysql> SELECT\r\n sum(stat_value) pages,\r\n table_name part,\r\n index_name,\r\n sum(stat_value) * @@innodb_page_size size\r\nFROM\r\n mysql.innodb_index_stats\r\nWHERE\r\n table_name LIKE 't#P#%'\r\n AND database_name = 'test'\r\n AND stat_description LIKE 'Number of pages in the index'\r\nGROUP BY\r\n table_name, index_name;\r\n\r\n+-------+--------+-----------------+----------+\r\n| pages | part | index_name | size |\r\n+-------+--------+-----------------+----------+\r\n| 2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 |\r\n| 1251 | t#P#p0 | a | 20496384 |\r\n| 1251 | t#P#p0 | b | 20496384 |\r\n| 2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 |\r\n| 1251 | t#P#p1 | a | 20496384 |\r\n| 1251 | t#P#p1 | b | 20496384 |\r\n| 2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 |\r\n| 1251 | t#P#p2 | a | 20496384 |\r\n| 1251 | t#P#p2 | b | 20496384 |\r\n| 2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 |\r\n| 1251 | t#P#p3 | a | 20496384 |\r\n| 1251 | t#P#p3 | b | 20496384 |\r\n+-------+--------+-----------------+----------+\r\n12 rows in set (0.00 sec)<\/pre>\n<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL provides commands to see the overall index size versus the data size. One of them is “show table status” : mysql> show table status like ‘t’\\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: … Continue reading