Find fragmented tables in MySQL

Via Mindraven:

Over time some of your MySQL tables may end up fragmented. If you run any type of diagnostic script like mysqltuner, it will even tell you how many of your tables are fragmented. You can easily fix this by optimizing the fragmented tables. The problem is, you might not know which tables are fragmented.

Here’s a quick little query you can run that will give you the tables that are fragmented and how badly fragmented they are:

select TABLE_NAME, TABLE_SCHEMA, Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema', 'mysql') and Data_Free >0;

Just in case anyone didn’t catch the optimize bit above. Once you’ve found your fragmentend tables, you can fix them with the following query, replacing %TABLENAME% with the actual table name:

optimize table %TABLENAME%