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?

Succession

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;

Azucenaazure

Slight changes required to work for me, but great approach. 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; – Wapiti

You need some backticks cat-ed in there around table_schema and table_name, otherwise i owe you a beer, many thanks. – Sustentation

Answer 2

ok, this is an old post, but this is needed.

Phil Dufault http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/ has written a wonderfull script, that is on github: https://github.com/pdufault/mysqlfragfinder/blob/master/mysqlfragfinder.sh

vim mysqlfragfinder.sh
#copy paste from github
chmod +x ./mysqlfragfinder.sh
./mysqlfragfinder.sh --user root_username --password root_password

and that is all. Have been using it on a number of servers for a while now.

Knifeedged

Answer 3

I know there is a answer. But MySQL has this recommendation for INNODB DB's:

It can speed up index scans if you periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:

ALTER TABLE tbl_name ENGINE=INNODB

Found here http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

Yorgo