Many popular PHP applications like WordPress and Textpattern, along with forum software like Invision Powerboard use MySQL databases to store their data and over time these databases require some maintenance. For example if you have deleted a lot of posts from your blog or forum recently, you might consider optimizing your database tables to improve performance.

This tutorial explains how you can run the Optimize Table command in cPanel.

Warning!!! Before you attempt to do this tutorial on your own data, you should backup your MySQL databases either from within cPanel or through your own program, if it has a backup database function. You should not attempt to do this procedure if your software has its own database maintenance functions, like Invision Powerboard which has its own database options in the Administrator panel. Proceed at your own risk!

First log into cPanel and open up the MySQL Databases manager icon. At the bottom of this screen you will find a link for phpMyAdmin. Clicking on this link will open up a new browser window with the home screen for phpMyAdmin.

On this screen, click Databases. This will list all the databases you have setup. Click on the database you want to work on. The database screen will list the tables for the given database.

Click the Check All link on the bottom left. Then from the pulldown menu choose Optimize Table.

PhpMyAdmin will give you some information once the operation is done. Close the window and then logout of cPanel on the original browser window. Close out of your browser completely to end the session.

If you are wondering what the Optimize Table command actually does, according to the MySQL Manual it basically defragments your database:

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it’s not likely that you need to do this more than once a week or month and only on certain tables.

Note that it is possible to run phpAdmin without going into cPanel, but for the sake of simplicity, this is the easiest way for most cPanel users to find phpMyAdmin.

For more information:

phpMyAdmin: This is the homepage for phpMyAdmin.

WordPress Optimize Your Database: an interesting post describing why you should optimize your WP database.

cPanel User Guides & Help: more links for learning cPanel.