« Back to overview
MySQL tables can get fragmented, especially on large tables this can be a problem and may impact performance.
Here is a query that shows fragmentation.
select
ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, round(DATA_FREE/ 1024/1024) / ((Round(DATA_LENGTH/1024/1024)) + ( round(INDEX_LENGTH/1024/1024))) as frag_ratio
from information_schema.tables where DATA_FREE > 0
ORDER BY
round(DATA_FREE/ 1024/1024) / ((Round( DATA_LENGTH/1024/1024)) + ( round(INDEX_LENGTH/1024/1024))) DESC
;
Also see:
- Drop all tables in a mysql (dev) database
- mysqldump and mysql import scripts
- Compare Diff for mysql schema differences between two databases
- Select/shows the row format of InnoDB tables in an sql query
- Doctrine paginator slow in Symfony
- Large mysql imports - wait timeout
- MySQL Set up quick start: create users and permissions