How can I defragment tables in MySQL?

Question

I have a database named COUNTY. COUNTY has a fragmented table GEO. I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error

"Table does not support optimize, doing recreate + analyze instead OK".

Then I executed ALTER TABLE GEO ENGINE='InnoDB'; and then went for ANALYZE TABLE GEO. But still it has fragmentation.

At last I dropped it and created it again. Still it has fragmentation. Can anyone please help me, how can I defragment it?

Answer 1

ASPECT #1

The message you saw did not say

"Table does not support optimize, do recreate + analyze instead OK"

but the message said

"Table does not support optimize, doing recreate + analyze instead OK"

This means the InnoDB Storage Engine already executed

ALTER TABLE GEO ENGINE='InnoDB';
ANALYZE TABLE GEO;

There was no need to do it again.

ASPECT #2

There is a reason why fragmentation still exists

If the primary key of the table is an integer with auto_increment, all the numbers generated for the primary key are in ascending order.

When the primary key was being loaded into the BTREE index, fragmentation was made. Why?

You may find this surprising, but I wrote an answer to a post about 3 years ago entitled How badly does innodb fragment in the face of somewhat out-of-order insertions?

In that post I explained how AVL binary trees will cause height rotation about 45% of of the time. For a BTREE, this would be represented as BTREE page splits. What does means for sorted data ?

• When loading data in ascending order, tree pages that split to the left would always be half full
• When loading data in descending order, tree pages that split to the right would always be half full

Those half full pages is what is manifesting the fragmentation.

This is further illustrated in another post I wrote last year (Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?) where I mentioned this same issue for MyISAM.

CONCLUSION

The only way to really reduce fragmentation would be to drop all indexes and recreate the indexes. If the tables are gigantic, this is not worth your time.

Answer 2

Tables won't have zero fragmentation in the best of circumstances. You've done everything recommended to reduce its fragmentation. It's time to move on to the next problem.