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?

score 39 · Accepted Answer · edited Oct 09 '12 at 10:01

39

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;

edited Oct 09 '12 at 10:01

Yes Barry

9,514
5
50
69

answered Jun 23 '09 at 15:15

Max Bube

406
4
3

9

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; – El Yobo May 26 '10 at 13:11
You need some backticks cat-ed in there around table_schema and table_name, otherwise i owe you a beer, many thanks. – Question Mark Jan 04 '14 at 02:11

score 1 · Answer 2 · edited Oct 27 '12 at 02:30

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.

score 0 · Answer 3 · answered Oct 08 '13 at 21:31

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

Mysql OPTIMIZE TABLE for all fragmented tables

3 Answers3