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