Mysql OPTIMIZE TABLE for all fragmented tables

Question

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?

Answer 1

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;