I have a question about tables and fragmentation in MySQL

My starting point is this question:

How find and fix fragmented MySQL tables

I have a table with 1 million of rows and 128MB of space. This table is partitioned.

I dump all the rows to a file with mysqldump, create the same table with '__BAK' suffix, and then insert all the data from the file to the '__BAK' table.

With this operation, the table space reduce to 112MB. Ok, I expected it.

But from the link above, I check fragmentation index (or ratio) and get these results:

"ENGINE" "TABLE_NAME" "data_length" "index_length" "data_free" "frag_ratio"

"InnoDB" "mytable" "100" "28" "26" 0,2031

"InnoDB" "mytable__BAK" "85" "27" "43" 0,3849

The frag ratio got worse in the new table, with supposedly all the rows together.

So, my questions are:

  • Is this because of the partitioning? Should I check frag ratio in partitioned tables?
  • Maybe it's not necessary to defrag a table with this low frag ratio, isn't it?
  • The steps I have described above, are right to reduce fragmentation and/or reduce size?
  • If I would have to choose an option, which one should I? First table (less frag ratio, more space) or second one (more ratio, less space)?

Any contribution or idea is very appreciated. Thanks a lot.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.