Mysql OPTIMIZE TABLE for all fragmented tables

Question

Id like to run optimize on all currently framgmented tables. These tables should be those that 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 will have to write external code to do this.

Answer 2

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';