Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql OPTIMIZE TABLE for all fragmented tables

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?

like image 872
Wienczny Avatar asked Jun 23 '09 11:06

Wienczny


People also ask

Does optimize table lock table?

OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. Otherwise, MySQL locks the table during the time OPTIMIZE TABLE is running.

Is MySQL optimize table Safe?

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.

How do I defrag a table in MySQL?

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.


1 Answers

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;
like image 147
Max Bube Avatar answered Oct 05 '22 23:10

Max Bube