I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries.
I am familiar with that formula that calculates the ratio between the max space available for a table VS the space used by data and indexes.
However my main questions are still unanswered. Perhaps this is due to the fact that I am familiar with index maintenance in SQL Server, and I tend to think that in MySQL it should be somehow similar.
In SQL server, you can have several indexes, and each one of it can have different levels of fragmentation. Then you can pick up one and perform a 'REORGANIZE' or 'REBUILD' operation in that particular index, without affecting the rest.
To the best of my knowledge, there is no 'table fragmentation' as such, and SQL Server doesn't provide any tool to fix the 'table fragmentation'. What it does provide are tools to check index fragmentation (understood like the ratio between the number of pages used by an index VS the fullness of that page and contiguity), as well as the internal and external fragmentation.
All of that is quite straightforward to understand, at least for me.
Now, when it comes the turn to maintain indexes in MySQL, there only exist the concept of 'table fragmentation, as mentioned above.
A table in MySQL can have several indexes, but when I check the 'fragmentation ratio' with that famous formula, I don't see the fragmentation of each index, but the table as a whole.
When I want to optimize the indexes in MySQL, I don't choose a particular index to operate on (as in SQL Server). Instead, I do an 'OPTIMIZE' operation in the whole table, which presumably affects all the indexes.
When the table is optimized in MySQL, the ratio between the space used by data + indexes VS the overall space is reduced, which suggest some kind of physical re-organization in the hard drive, which translates into a reduction of the physical space. However, index fragmentation is not only about physical space, but the structure of the tree that has been changed over the time due to inserts and updates.
Finally, I got a table in InnoDB/MySQL. That table has 3 million records, 105 columns and 55 indexes. It is 1.5GB excluding indexes, which are 2.1GB.
That table is being hit thousands of times ever day for updating, insertion (we don't actually delete records).
That table has been created years a go and I know for sure that nobody is maintaining indexes whatsoever.
I was expecting to find a huge fragmentation in there, but when I perform the fragmentation calculation as prescribed
free_space / (data_length + index_length)
it turns out that I have only a 0.2% fragmentation. IMHO that is quite unrealistic.
So the big questions are:
- How do I check fragmentation of a particular index in MySQL, not the table as a whole
- Does OPTIMIZE TABLE actually fix the internal / external fragmentation of an index as in SQL Server?
- When I optimize a table in MySQL, does it actually rebuilds all the indexes on the table?
- Is it realistic to think that reducing the physical space of an index (without rebuilding the tree itself) actually translates into a better performance?