How to calculate a specific InnoDB index size ?

MySQL provides commands to see the overall index size versus the data size.

One of them is “show table status” :

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.

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 :

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 :

 

 

This entry was posted in tip. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *