How to Optimize a MySQL Database using phpMyAdmin

• Optimize Database
• Repair 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, such as VARCHAR, TEXT, BLOB or VARBINARY columns.  Deleted rows are maintained in the linked list and insert operations reuse the old row positions.  You can use "Optimize Table" to reclaim unused space and defragment the data file for optimal performance.  If a lot of changes have been made to a table, optimizing the table can sometimes significantly improve performance.

Step 1: Login to phpMyAdmin

cPanel: (Shared, Reseller, SEO, Linux Dedicated, VPS with cPanel)

1. Login to cPanel
2. Click on the phpMyAdmin button in the Databases section.

Plesk 10: (Windows Shared, Windows Dedicated, VPS with Plesk)

1. Login to Plesk
2. Click on the Websites & Domains tab.
3. Click on the Databases icon.
4. Click on the database you want to manage.
5. Click on the Webadmin icon.

Step 2: Optimize Tables

1. Select the database you want to optimize from the list in the left column, which should take you to the "structure" tab for the database.
2. Select the tables you wish to optimize by checking the check box in front of each one, or clicking on Select All if you want to optimize all of them.
3. On the drop down box that says "with selected..." select "Optimize Table."  This will optimize the table and take you to a new screen.

How to Repair a Broken Database

• Optimize Database
• Repair Database

If you get the following error, it can easily be fixed from within cPanel:

cpaneluser_database is marked as crashed and needs to be repaired

Checking the Database

Even with different kinds of errors such as "supplied argument is not a valid" or "table attribute does not exist," the following steps can be used with this built-in cPanel test to try fixing the issue.

1. Log into cPanel and navigate to the Databases section.
2. Click on the MySQL Databases icon.
3. Under Modify Databases, look for the drop-down menu called Check DB.
4. Select the database name which is having trouble.
5. Click the Check DB button.

Repairing the Database

The checking process should reveal the cause of the problem. If the problem is "marked as crashed," then proceed with the steps listed below:

1. Go back to MySQL Databases.
2. Under Modify Databases, look for the drop-down called Repair DB.
3. Select the database name which is having trouble.
4. Click the Repair DB button.
5. Once complete, reload your web site.