I have a question about tables and fragmentation in MySQL
My starting point is this question:
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 (last column) 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.
Maybe I'm playing around fragmentation.
In summary, do you know any good practice or recommendations about MySQL fragmentation (in partitioned tables or not)?