First, create a query to generate all of the needed SQL statements to optimize each table in the DB.
The output generates an optimization query for each table.
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”
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.
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.
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.