Optimize Your MySQL Database Tables

3rd March 2009

You can optimize MySQL tables using the OPTIMIZE command. This can be used if you have a table with any variable length rows and you make lots of changes to the data in that table. Variable length columns are VARCHAR, VARBINARY, BLOB, or TEXT. The OPTIMIZE TABLE will defragment the data and reclaim any unused space. This also has the effect of resetting any auto incrementing numbers to the highest value in the table.

  1. <p>You can do this for every table in the database, but to save time MySQL comes with a command line script that will optimize all tables in a database. This program is called mysqlcheck and can be run like this.</p>
  2.  
  3. <pre language=">mysqlcheck -o database -u user -ppassword

Note that when writing this command you should not put a space in between the -p and your password.

The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.

Optimizing tables will clear out unnecessary settings and speed up your tables slightly, but there is little point in doing this more than once a month, or once a week for very high traffic MySQL servers.

Add new comment

The content of this field is kept private and will not be shown publicly.