How to figure out size of Indexes in MySQL
MysqlMysql Problem Overview
I want to determine the size of my indexes, they are primary key indexes. This happens to be on mysql cluster but I don't think that is significant.
Mysql Solutions
Solution 1 - Mysql
I think this is what you're looking for.
show table status from [dbname]
http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
Solution 2 - Mysql
Extending Vajk Hermecz's answer.
This is how you can get all of the indexes size, in megabytes, without the PRIMARY (which is the table itself), ordered 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 size_in_mb DESC;
Solution 3 - Mysql
If you are using InnoDB tables, you can get the size for individual indexes from mysql.innodb_index_stats. The 'size' stat contains the answer, in pages, so you have 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';
Solution 4 - Mysql
Here's an adaption from some of the above to also give you the percentage of the total index for the table that each index has used, hopefully this will be useful for someone
select
database_name,
table_name,
index_name,
round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,
round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis
where stat_name='size'
and table_name = 'TargetTable'
and database_name = 'targetDB'
Example output
database_name table_name index_name SizeMB Percentage
targetDB TargetTable id 10 55.55
targetDB TargetTable idLookup 5 27.77
targetDB TargetTable idTest 3 16.66
Regards Liam
Solution 5 - Mysql
On MyISAM, each index block is 4 KB page filled up to fill_factor with index records, each being key length + 4 bytes long.
Fill factor is normally 2/3
As for InnoDB, the table is always clustered on the PRIMARY KEY, there is no separate PRIMARY KEY index
Solution 6 - Mysql
Using phpMyAdmin, when viewing the table structure there is a Details link at the bottom somewhere. Once you click on it it will show you the total size of the indexes you have on the table where it is marked Space Usage.
I don't think it shows you each index individually though.
Solution 7 - Mysql
In this article determine how to calculate index size. http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/
Solution 8 - Mysql
SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name='t1'
AND stat_name = 'size' GROUP BY index_name;