MySQL provides commands to see the overall index size versus the data size.
One of them is “show table status” :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> show table status like 't'\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4186170 Avg_row_length: 34 Data_length: 143310848 Max_data_length: 0 Index_length: 146030592 Data_free: 6291456 Auto_increment: NULL Create_time: 2014-02-04 15:40:54 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
So here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates :
Data_length: 143310848, 136Mb clustered index size.
Index_length: 146030592, 139Mb secondary index size.
In this example, I have 3 indexes : 1 auto-generated clustered index and 2 secondary indexes.
|
1 2 3 4 5 6 7 |
CREATE TABLE `t` ( `a` smallint(6) DEFAULT NULL, `b` smallint(6) DEFAULT NULL, `c` smallint(6) DEFAULT NULL, KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
In 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 …
From 5.6, there is a better way : this is the hidden 5.6 gem I want to share with you :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
ANALYZE table t; SELECT sum(stat_value) pages, index_name, sum(stat_value) * @@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name = 't' AND database_name = 'test' AND stat_description = 'Number of pages in the index' GROUP BY index_name; +-------+-----------------+-----------+ | pages | index_name | size | +-------+-----------------+-----------+ | 8747 | GEN_CLUST_INDEX | 143310848 | | 4456 | a | 73007104 | | 4457 | b | 73023488 | +-------+-----------------+-----------+ 3 rows in set (0.00 sec) |
Using 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.
If you need exact values, run ANALYZE TABLE 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.
Regarding index stats, each index has a stat called ‘Number of pages in the index‘. The index size can be obtained by multiplying this value by the InnoDB page size.
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. The MySQL manual should also soon be updated with this useful information.
Note that the tip also works with partitioned table :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
mysql> alter table t partition by key(c) partitions 4; Query OK, 4194308 rows affected (44.03 sec) Records: 4194308 Duplicates: 0 Warnings: 0 mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` smallint(6) DEFAULT NULL, `b` smallint(6) DEFAULT NULL, `c` smallint(6) DEFAULT NULL, KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (c) PARTITIONS 4 */ 1 row in set (0.01 sec) ANALYZE TABLE t; SELECT sum(stat_value) pages, index_name, sum(stat_value) * @@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name LIKE 't#P%' AND database_name = 'test' AND stat_description LIKE 'Number of pages in the index' GROUP BY index_name; +-------+-----------------+-----------+ | pages | index_name | size | +-------+-----------------+-----------+ | 8848 | GEN_CLUST_INDEX | 144965632 | | 5004 | a | 81985536 | | 5004 | b | 81985536 | +-------+-----------------+-----------+ 3 rows in set (0.00 sec) mysql> SELECT sum(stat_value) pages, table_name part, index_name, sum(stat_value) * @@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name LIKE 't#P#%' AND database_name = 'test' AND stat_description LIKE 'Number of pages in the index' GROUP BY table_name, index_name; +-------+--------+-----------------+----------+ | pages | part | index_name | size | +-------+--------+-----------------+----------+ | 2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p0 | a | 20496384 | | 1251 | t#P#p0 | b | 20496384 | | 2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p1 | a | 20496384 | | 1251 | t#P#p1 | b | 20496384 | | 2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p2 | a | 20496384 | | 1251 | t#P#p2 | b | 20496384 | | 2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p3 | a | 20496384 | | 1251 | t#P#p3 | b | 20496384 | +-------+--------+-----------------+----------+ 12 rows in set (0.00 sec) |