How to Check MYSql Indexes Size on Database

The Index is very important mechanism to improve mysql performance but some cases indexes size could be increase than available memory of the application.So first question in mind how to check how much RAM available for index and how much consume memory your database index.In this tutorial i will described how to get size of indexes in mysql for two type engine InnoDB and MyISAM.

MyISAM

Available RAM for indexes :
We can get Available RAM for indexes through below MySQL variable:
key_buffer_size

Required Memory for Index:
We can get Required RAM for indexes with help of below SQL:
SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMBInDB
FROM information_schema.tables WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','performance_schema','mysql');

InnoDB

Available RAM for indexes :
We can get Available RAM for indexes through below MySQL variable:
innodb_buffer_pool_size
Required Memory for Indexes:

We can get Required RAM for indexes with help of below SQL:

SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB
FROM information_schema.tables WHERE engine='InnoDB';