Optimize All Tables In a MySQL Database

September 29, 2013 by Sean Pinegar

In a previous post I covered converting all of your MySQL MyISAM tables to InnoDB. Lets take that a step further and optimize all of the tables in the DB.

First, create a query to generate all of the needed SQL statements to optimize each table in the DB.

SET @DB_NAME = 'db_name'; SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') AS sql_statements FROM information_schema.tables AS tbl WHERE table_schema = @DB_NAME AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;

The output generates an optimization query for each table.

OPTIMIZE TABLE accounts; OPTIMIZE TABLE accounts_audit; OPTIMIZE TABLE accounts_bugs; OPTIMIZE TABLE accounts_cases; OPTIMIZE TABLE accounts_contacts; OPTIMIZE TABLE accounts_cstm; OPTIMIZE TABLE accounts_opportunities; OPTIMIZE TABLE accro_account_roles; OPTIMIZE TABLE accro_account_roles_accounts_c; OPTIMIZE TABLE accro_account_roles_audit; OPTIMIZE TABLE accro_account_roles_contacts_c; ...

Copy and paste these results and run the queries. After some time all of the tables in the DB will be optimized.

One thought on “Optimize All Tables In a MySQL Database”

Ian Winter says:

August 1, 2014 at 6:19 am

Remember though that OPTIMIZE isn’t just that for InnoDB, it’s actually an ALTER. On large tables that can make a big difference and in a replicated world can also create some extra consideration.

From http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE … FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index.

Reply

@seanpinegar

Optimize All Tables In a MySQL Database

One thought on “Optimize All Tables In a MySQL Database”

Leave a Reply Cancel reply