How to Check MYSql Indexes Size on Database

The Index is very important machanism to improve mysql performance but some cases index size could be increase than available memory for 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 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’;

The following two tabs change content below.
Hi, This is Parvez Alam from India. I am software developer with 4 years’ experience in web development. I have submitted articles on PHP, Mysql, Magento,CSS, HTML, jQuery, web designing and social API. You can subscribe to my blog via RSS/Twitter/Google plus and Facebook. parvez1487(at)gmail(dot)com

Latest posts by Parvez (see all)

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>