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 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.