How To Optimize Your MySQL Database For Better Performance
Saturday, January 30, 2010, 1:33 AM | 8 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.
3 Trackback(s)
- Jan 30, 2010: How to optimize your MySQL database for better performance … | Drakz Free Online Service
- Jan 30, 2010: How to optimize your MySQL database for better performance … Drugs on Me
- Sep 17, 2010: Moving My Sub-Domain To Add-On Domain Is Complete
8 Responses to “How To Optimize Your MySQL Database For Better Performance”
By TSwain on Feb 1, 2010, 8:42 pm | Reply
I don’t usually reply to posts but I will in this case, great info…I will add a backlink and bookmark your site. Keep up the good work!
By Robert Shumake on Feb 2, 2010, 10:19 am | Reply
Your site was extremely interesting, especially since I was searching for thoughts on this subject last Thursday.
–Robert Shumake Paul Nicoletti
By WP Themes on Feb 3, 2010, 5:49 pm | Reply
Good fill someone in on and this enter helped me alot in my college assignement. Thank you as your information.
By SatelliteTVforPC on Feb 10, 2010, 5:54 am | Reply
hey. Great Blog you’ve got here. I’ve been tooling around trying to find some new information and I think i found it! Thanks a lot!
I have a website too. You can check it out at Satellite TV for PC
By FcAf on Jul 2, 2013, 9:59 pm | Reply
Very interesting subject , regards for putting up.