Define table fragmentation in MySQL explain how it occurs it

Define table fragmentation in MySQL, explain how it occurs, its impact on performance, and describe different ways of eliminating table fragmentation, and their applicability to different storage engines.

Solution

Table fragmentation: Whenever large number of deletes happen to the table, it will leave lot of space unused, this unused space will lead to fragmentation.

MySQL tries to scan the unused data, it checks the starting point at which data can be added for new inserts to claim the fragmented space, but sill it will leave the gap open. This extra fragmented space will make the select query that is reading against the table run more time that is going for a table scan instead of using index space/scan.

Eliminating table fragmentation: Run optimize table to defragment the tables in MySQL. This will clear the unused space and provide better performance. Ensure to take the database back up before running this operation.

Alternative, is to take the table dump, drop and recreate the table and loading the table with backup data also remove the fragmentation.

Applicability to different storage engines : Not all storage engines of MySQL support the optimize table maintenance program. MyISAM and InnoDB are two main storage engines which support defragementation. Memory, CSV, Archive do not support the optimization.