MySQL: Fragmentation of Data | Indexes | How to Defragment

Fragmentation refers to the phenomenon where the data and indexes within a table are scattered or fragmented across the storage space. It occurs when data is added, modified, or deleted within the table over time. Fragmentation can lead to inefficient disk usage and slower query performance.
In this video, we will see how to reclaim the space and defragment the tables and Indexes.

Script to check fragmentation:
SELECT ENGINE,
CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_length,
ROUND(INDEX_LENGTH/1024/1024, 2) AS index_length,
ROUND(DATA_FREE/1024/1024, 2) AS data_free,
(data_free/(index_length+data_length)) AS frag_ratio
WHERE DATA_FREE 'You need to enter greater than symbol here' 0
ORDER BY frag_ratio DESC;