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?
OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. Otherwise, MySQL locks the table during the time OPTIMIZE TABLE is running.
OPTIMIZE is 'safe' in all respects since it locks the table, copies all the data over, then renames the new copy in place of the old.
Both ALTER TABLE tbl_name ENGINE=INNODB and ALTER TABLE tbl_name FORCE use online DDL. For more information, see Section 15.12, “InnoDB and Online DDL”. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With