How to optimize your MySQL database for better performance

Sat Jan 30, 2010, 1:33 am | 7 Comments

This post is dedicated to the folks who have recently come on board the bandwagon of blogging on WordPress platform. I have recently started to optimize my blog database and I do it frequently, like at least twice a month. It’s simple and doesn’t require a great in-depth knowledge of anything in particular. You don’t have to know anything about database anyway.

However, before you embark on the journey of MySQL database optimization, I suggest you back up your database. The CPanel has that too under the heading Files. You just have to dig in more deeply. But the suggestions below are easy and innocent enough that I am sure you would do it with passing color.

When you keep adding your posts and publishing them, the database has tables and rows and columns that it keeps filling up. You do deleting, adding, modifying, on a constant basis, without knowing what happens to the inner workings of the database. After a while, it becomes sluggish and affects its performance adversely.

That’s where the Optimization phase comes in. I do it manually. I have not installed any plugins for that. The reason is to use the particular plugin, you have to know the details of the database. I sure don’t. I use a tool called PhpMyAdmin which you would see when you log in to your CPanel, scroll down and you would see it under the heading “Databases.” By the way, PhpMyAdmin is a free software tool written in PHP that handles the administration of MySQL database.

Click on PhpMyAdmin and you would see a panel where in the left column you should see the Information_Schema and just under it is the name of your database. Select your database by clicking on it.

You then should see a screen-full of window. In one of the columns, on my CPanel the rightmost column, is your culprit better know as Overhead. You want to remove any numerical value in that column across a table name.

What I do is select one of the options: Check All/Uncheck All/Check tables having overhead. I select the third option. To the right of it, find in a small window With selected. Click the arrow beside it and you would see a list of all the options available. Carefully select Optimize table from the options. I say carefully, because the moment you select an option on the list, in this case, Optimize table, the utility immediately start its required action without any more questions asked.

After optimization, you should see all the overhead numbers across the tables removed. Then you know what I do? I have two legs of own and I borrow two more and I run as fast as I can and I get the hell outta there before I break anything.

In a Nutshell
That’s all I know and that’s all I do. I have not tried doing anything else lest I break anything in my beloved database that I worked so hard to fill up. It’s simple and easy but at the same time you must be extremely careful not to screw anything up.

What do you think?

Related Posts On Doable Finance dot Com

1. 3 Tips For Better Performance Of Your Investment Portfolio

Click on the left to read more similar articles.

• Blogging How-To