How To Optimize Database Tables To Make Your Pages Load Faster

As time goes by and our websites receive traffic, it is inevitable that the associated databases start to build up. The consequence of this is that websites become slower to respond and load, which as we said earlier affects negatively the user experience of our visitors.

From time to time we need to perform some housekeeping in our databases in order to get them back to normal state and get rid of the overhead which builds up in time.

We can optimize our databases manually using phpMyAdmin or by using a plugin. If you are familiar with phpMyAdmin I would suggest using that and avoid installing yet another plugin in your Wordpress site. But if on the other hand you don't feel comfortable with phpMyAdmin, then a plugin is definately a safer option for you.

If you want to use plugins:

Then I recommend WP Database Optimizer which allows you to schedule your optimization activities as well as the WP-Optimize which allows you to remove comments in the spam queue or un-approved comments with a single click.

Then of course there is Clean Options that, as described in the previous page, finds orhaned options and allows the removal of them from the wp_options table.

If you want to use phpMyAdmin (the old fashioned way):

If you think that you want to get your hands a little more dirty then open up phpMyAdmin and from the left hand column click the database of your website. Then you will see all the tables belonging to your database and on the last column on the right, you can see the overhead of each table.

What is the overhead: Well, when you delete a row from a table, the data is only marked as deleted but it is not physically removed from the disk. This creates an "overhead" of empty space in the file that holds that table. Of course when new rows are inserted they may be inserted in places to fill this "gap" but not necessarily. In other words, the only way to remove this "overhead" is by optimizing the table.

So, STEP 1:

Have a look at the overhead column on the right and find out which tables have an actual value.




STEP 2:

Check all the tables that have an overhead and need to be optimized. After that select "optimize table" from the drop down menu and wait until all your tables are optimized. It shouldn't take more than a second (depending on the size of your database of course).

You should see something like the following (depending on the tables you chose):

That's it: Your tables are now optimized and will contribute to your website's speed and efficiency.