Is there any way to find out the fragmentation of indexes in MySQL ?
If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt.
I'm using MySQL 5.1 on Windows Server 2008
Can somebody please help me.
MySQL Index Fragmentation
fragmentationindexMySQL
Related Question
- MySQL indexes maintenance
- Sql-server – Index fragmentation increased significantly after rebuild
- Sql-server – understanding index fragmentation statistics
- Sql-server – Query to see Index Fragmentation on all databases without using SP or temp tables
- Sql-server – Is fragmentation at non-leaf levels a problem
- Sql-server – Index automation job script
Best Answer
For InnoDB tables, this is both possible and redundant. It is possible using Percona Server's
INNODB_INDEX_STATStable inINFORMATION_SCHEMA. This table shows you the number of pages per index in an InnoDB tables.Well, apparently InnoDB manages its own pages very well. Upon purging of data it merges back its index nodes. I have done multiple tests to verify that an
OPTIMIZE TABLE-- a complete rebuild of the table -- does not actually improve on index fragmentation.Please see my post on the subject.
By the way, I believe MySQL 5.6 has a similar table built-in.
With regard MyISAM tables, I'm unaware that there's any way of looking at the fragmentation.