Mysql OPTIMIZE TABLE for all fragmented tables

defragmentation, fragmentation, mysql, optimization

Solution

You can do something like this:

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE '/tmp/optimize.sql';

Alternatively, if the first one fails, try:

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM information_schema.tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE /tmp/optimize.sql;

Problem

I'd like to `OPTIMIZE` all currently fragmented tables. These tables should have `information_schema.DATA_FREE > 0`. Is it possible to optimize all tables with this property in one command in SQL or will I have to write external code to do this?

Original source