Optimize All Tables In a MySQL Database

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.

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.

 

This entry was posted in Uncategorized. Bookmark the permalink.

One thought on “Optimize All Tables In a MySQL Database

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *