How to determine index size in MySQL

Question

I want to determine the size of my indexes, these are primary key indexes. This happens in a mysql cluster, but I don't think this is important.

Answer 1

I think this is what you are looking for.

show table status from [dbname] 

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 2

If you use InnoDB tables, you can get the size for individual indexes from mysql.innodb_index_stats . The "size" statistics contain the answer on the pages, so you need to multiply it by the page size, which is 16K by default .

 select database_name, table_name, index_name, stat_value*@@innodb_page_size from mysql.innodb_index_stats where stat_name='size'; 

Answer 3

Extension of the answer by Wike Hermez.
So you can get the whole size of indexes in megabytes without PRIMARY (which is the table itself), sorted by size.

 SELECT database_name, table_name, index_name, round(stat_value*@@innodb_page_size/1024/1024, 2) size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' ORDER BY 4 DESC; 

Answer 4

In MyISAM each 4 KB index block is filled up to fill_factor with index entries, each of which has a key length + 4 .

Fill factor usually 2/3

As for InnoDB , the table is always clustered on PRIMARY KEY , there is no separate PRIMARY KEY index

Answer 5

Not used by me, but maybe MySQL Index Analyzer might be useful.

Answer 6

Using phpMyAdmin, when viewing the table structure, there is a Details link at the bottom. After you click on it, it will show you the total size of the indexes that you specified in the table that says "Space Usage".

I don't think it shows you each index individually, though.

Answer 7

In this article, determine how to calculate the size of the index. http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/