2

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.

3
  • Are you using InnoDB, MyISAM, or a mixture of both ??? Feb 11, 2013 at 16:55
  • If you are using InnoDB, do you have innodb_file_per_table configured ??? Feb 11, 2013 at 16:56
  • I'm using InnoDB with "innodb_file_per_table" Feb 12, 2013 at 4:22

1 Answer 1

2

For InnoDB tables, this is both possible and redundant. It is possible using Percona Server's INNODB_INDEX_STATS table in INFORMATION_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.

4
  • Thanks for your comment. But as far as i know, the Percona server is available for Linux systems only. Feb 11, 2013 at 8:32
  • Yes, you are correct. I should have noticed that. Feb 11, 2013 at 17:02
  • The number of index blocks can be misleading since it usually includes pre-allocated, but empty, blocks.
    – Rick James
    Jul 29, 2015 at 22:09
  • Also rebuilding a table can increase index performance by magnitudes, innodb doens't do that on it's own. (8.0.13)
    – John
    Jan 26, 2019 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.