Re: Index file growth - is it a bug?

[Apachez]

That is because the order of your datafile after a while is no longer optimal and the index has to compensate for this. This means that the pointers in the indexfile needs more indexspace on the disk in order to point out the right row in the datafile back and forth for each entry in the index.

A way to fix this without having to dump table and then read it back is to use the ALTER TABLE command along with ORDER BY such as:

ALTER TABLE t1 ORDER BY col1, col2, col3;

The order should of course be according to how your indexes are created or how your table was originally created. Because in above example an index such as (col2, col3) will take less indexspace on the disk than an index such as (col3, col1) if above example were used to order the datafile.