Different MySQL index sizes after restore

Question

I've been trying to create a backup for a very large MyISAM DB with mysqldump and I've noticed that some of the tables have variations in index sizes after restoring.

For example, one of my larger tables has an index length of 50316242944 and the restored dump of that table has an index length of 50227628032

I've run optimize table and have done a checksum and everything seems to be fine. I've even run ALTER TABLE generic_dummy_table_name ORDER BY datetime; which I found on a similar question on this site.

Could someone explain to me why there is such a large difference in index length between my tables and if there is an easy way to fix it?

Answer 1

When you load a table, the order of the rows can affect the shape of an index.

If you ran ALTER TABLE generic_dummy_table_name ORDER BY datetime;, then any index whose first column is datetime, will be load in a lopsided fashion. Under that circumstance, doing ALTER TABLE ... ORDER BY some-column will always create a badly fragmented index.

Please see my Oct 26, 2012 post How badly does innodb fragment in the face of somewhat out-of-order insertions? where I explain how a BTree get fragmented when you order the data by a specific column in a table. Even though the post is about InnoDB, the BTree mechanics I discussed in that post apply just as much to MyISAM.

I also discussed these mechanics in Benefits of BTREE in MySQL (July 28, 2012)

Your only fix is to not mess with the physical order of rows when restoring a table.

YOUR INDEX SIZE

From the number in the question, your .MYI file is 50GB ??? You need to identify what indexes are redundant and remove them. Please goto MySql - find redundant indexes. There are two nice answers there how to find them and generate SQL to remove them.

Answer 2

Random insert into or delete from a secondary index can cause the index fragmentation. Basically it will use extra space than required. There are several ways to reclaim this space. One of the way to reclaim space is, taking mysqldump and restoring it. This helps to reclaim the space.

As far as the restoration completed without any error this should be fine and you don't need to worry.

For more information on fragmentation you can refer to below link. https://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html https://dev.mysql.com/doc/refman/5.6/en/myisam-optimization.html