16

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?

37

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

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.

0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["<li class=\"-item\"><a href=\"https://www.facebook.com/officialstackoverflow/\" class=\"-link\">Facebook</a></li>\r\n                    <li class=\"-item\"><a href=\"https://twitter.com/stackoverflow\" class=\"-link\">Twitter</a></li>\r\n                    <li class=\"-item\"><a href=\"https://linkedin.com/company/stack-overflow\" class=\"-link\">LinkedIn</a></li>\r\n                </ul>\r\n\r\n                <p class=\"mt-auto mb24\">\r\nsite design / logo &#169; 2019 Stack Exchange Inc; user contributions licensed under <a href=\"https://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>\r\n                            with <a href=\"https://stackoverflow.blog/2009/06/25/attribution-required/\" rel=\"license\">attribution required</a>.\r\n                    <span id=\"svnrev\">rev&nbsp;2019.2.22.32933</span>\r\n                </p>\r\n            </div>\r\n        </div>\r\n\r\n            </footer>\r\n            <script>StackExchange.ready(function () { StackExchange.responsiveness.addSwitcher(); })</script>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works b"], "copyright__copyright__copyright": ["a \u201cnull\u201d ALTER TABLE operation, which causes MySQL to rebuild the table:\n\nALTER TABLE tbl_name ENGINE=INNODB\n\nFound here http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html\n\nYour Answer\n\nBy clicking \"Post Your Answer\", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}