Optimize Your MySQL Databases

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.

phpMyAdmin Database Tables

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.

5 Comments

  1. […] Optimize your MySQL database. With time, database performance can degrade. Cleaning things up on MySQL isn’t hard–WebKeyDesign has a good guide (just make sure to have a backup in case things go wrong). In addition, I’ve found that dropping unused or unnecessary MySQL tables (especially if they are large) can make a huge difference. […]

  2. SQL, MySQL > this is just what I was looking for.

    I will add a link to this on the page above :)

    Great work thanking you.

  3. Hello,

    when I log into my phpMyAdmin, I see that “Overhead” column isn’t empty at all (unlike your picture).

    Should I do something with that?
    And what “optimize” will do – will it clear my table from “overhead”?

    All the best,
    Mike

  4. […] and then optimize it. You can do it through your hosts CPanel, phpMyAdmin or just shell commands. Here is a quick link walking you through the process using […]

  5. thomannguyen says:

    There are quite a few of your recommendations listed here that are handled by the W3 Total Cache plugin like combining and enhancing the CSS and Javascript files. They have really made some serious advances with this plugin, not to mention the use if a CDN which can really speed up your site.

Leave a Reply

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

*

question razz sad evil exclaim smile redface biggrin surprised eek confused cool lol mad twisted rolleyes wink idea arrow neutral cry mrgreen

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>