Don’t forget that your website is saved on a server, which is another computer. Sometimes your data gets corrupted, or sometimes the databases fail. To keep your WordPress database happy, it might be necessary to repair and optimize them.
Your web host isn’t always obligated to do this for you, but one of the more common issues of “Error establishing database connection” is something the host support ends up dealing with. It’s something that doesn’t usually take long, but if you can’t afford to have your website down while waiting for your web host to finally answer your support ticket, you can take matters into your own hands.
This article will cover ways to repair and optimize a WordPress database for both the developer and those who aren’t so code savvy.
How To Repair and Optimize A WordPress Database – The Easy Way
The easy way is using a plugin. Below are a few options you can try for your website. Make sure to only have these activated in use. It’s just better as a WordPress security option.
Also note that these plugins have more than optimize and repair features so make sure to back up your website before performing a clean up. If you accidentally drop tables, you’ve pretty much messed things up… so be careful and don’t rush through the process.
WP-DBManager – You can run SQL queries, drop or create tables, repair tables, optimize tables, and even back up your database.
WP-Optimize – This not only extends to repairing and optimizing the database, but to removing up trashed comments, removing trackbacks and pingbacks, clearing post trash, and even removing spam or unapproved comments.
Note: You can click on the small images to view them larger in a window.
How To Repair and Optimize A WordPress Database Using cPanel – For Developers
If you prefer not to use a plugin, and your web host uses cPanel, here are some instructions and a video.
1. Log into your cPanel
2. Scroll to the section called Databases and click on phpmyadmin.
3. Find your database, and open to browse the tables.
If you have several and don’t remember which one, go to your WordPress install’s wp-config.php file to get the database name.
4. Select the table or tables you want to repair or optimize.
5. Scroll to the bottom of the list. You will see a drop down field. Select Repair Tables.
6. If you want to optimize your tables too, go back and select the table or tables you just repaired. In the drop down field, you will then select Optimize Tables.
As a notation, if your web hosting uses Windows and Plesk as the control panel, the optimize and repair steps are nearly the same. Another note is that in some rare cases, if your site is really old and large, you could encounter an issue where the whole process of repairing optimizing may break. This is usually if you’ve never done this. Don’t panic- just contact your web host (preferrably through their live chat in order to quickly expedite the process) and they will take care of things for you.
Have you ever had to optimize and repair your WordPress database tables? How often do you do this? What method do you use?
My site went down for this very reason. Nile worked to get it up and then wrote this post so I know what to do in future. It’s very scary when it happens and nothing seems to fix it.
Lucky I had Nile to help!
I was happy to help. 🙂
The option is very good for people without experience in SQL and web development. WordPress DB need to be maintained all the time and especially if test and delete many plugins. Actually “repair” option itself wont fix almost anything on broken tables or missing keys.
Great tutorial Nile, I recent backup can solve issues too, problem may differ from one person to other.
Web hosting provider should handle this for those without knowledge into DBs.
I dunno. Still seems a bit scary and foreign stuff and worried I might completely jack something up. But you are right, sometimes you just have to take matters in your own hands. PowWeb will sometimes take a couple days to answer a support ticket and ‘live’ chat can take 20 minutes to get through.
I can’t believe Powweb is still around… lol
Well, i think the database repair via the cpanel is the easiest. I once had my database corrupteddue to some conflicting settings but finally i was able to repair it using cpanel.
I think it’s easier and also safer through cPanel as well.
Nile, the most important activity mentioned, back up your system. These are becoming more common, site crashes. They route from low to now managing. Updates not being performed.
You are right. We had so many for the last quarter. It drove out techs crazy. I was luck in always backing my system and programs up offsite. That was the best investment I made.
Thank you for your continued supportive insight. Nile, you are amazing!
Wow, it’s a sure sign that I have actually learned something over the past year because I actually had a clue what you were talking about while I was reading your article! Anyway, this is another of those issues the most bloggers would never think of until faced with having to deal with it, so thanks for providing such a comprehensive tutorial.
Thanks for posting such a useful and valuable article. This will surely do wonders for me.
I am grateful.
Great article and this is the easiet way i found to optimize the wordpress blog
Thanks for sharing
Nile, I did not know about this so thanks for all the education on how to do it. I will check with my hosting company first to see which they recommend. I will probably chose the easier option. I appreciate your info on how to do both options.
first thing, i want to say that i love your background. 🙂 thanks for the guide. being a beginner, i would love to learn from tips. i would like to say you thanks for this post.
Thanks for sharing!!! 🙂
These help anyone and me to repair wp database.
I use wp optimize for optimizing the database. The options available in this plugin are great. One thing what I liked about this that it deletes all the spam comments at once. This post is really nice.
I use few plugins for optimizing the database. few of them you have mentioned here Nile. This is amazing to come here in your blog Nile, you always came up with some interesting stuffs.
Good strategies here, Nile, for managing your database.
I know this stuff is scary to a lot of people. I can do these things, but it’s not really the best use of my time. That’s why I’ve moved my most important sites to managed hosting. It’s more expensive, but not prohibitive, and these issues “get handled” without me getting my hands dirty 🙂
Technically, it’s not your host’s duty to repair your databases. And in the case you get a busy live chat, you end up waiting for them to do it, when you can do it in less that 3 minutes. Just saying. You should be doing it regularly and if you don’t want to fool with code, there are plugins mentioned that are available.
I am personally using WP-DB Manager. I have installed this plugin on all of my clients WordPress websites. It is a serious love to me. It helps me a lot in resolving slow loading speed issues, furthermore, other compatibility issues.
Interesting Blog Nile!
I love the theme of your blog very eye catching!
thanks for sharing the Post!
cPanel is much saver!
great post again Nile!
Thanks for the post Nile. This is just what I needed as I’m definitely no expert in databases!
Optimizing database tables can be done manually using phpMyAdmin or by using a plugin. I’m of the opinion that if you don’t absolutely require a plugin to do it, then it’s best not to add another. However, if you’re not familiar with phpMyAdmin, then a plugin may be a safer option for you.
Alternativ to phpMyAdmin you can use the small plugin Adminer in WordPress directly, very fast and include the backend of your blog.From time to time it is a good idea to do a little housekeeping with your WordPress installation, especially if you’ve been experimenting with and deleting plugins. MySQL databases don’t clean themselves.Nice Post Really impressed by this post 🙂
I have a wordpress blog.. I repair my database by your method. My database size 58 MB , When i repair this it become 42Mb. Thanks for your nice article. I am very happy. 😛
As a Niche WordPress theme Developer, I always love to hack the database and the second version was more attracting to me. I do not love to install plugins for every second task as they start increasing our wp-option table that lead to slow website speed later on.
Hey nice tutorial! Thanks a lot!
Thanks for sharing 😀 . Now i can fix my site!
I’m way to afraid to sign into my cPanel and do this myself! I do however use the WP-DBManager plugin and haven’t had any issues with is so far.
Hey Nile, thanks for sharing.
But I would agree that this “optimization” should only be done by tech-savvy people because you can screw up your complete WordPress blog, I’ve learned the hard way. In this case you have to be better safe than sorry, so by all means, do not change anything in the WordPress database tables unless you have at least one complete and working backup.
I now know a thing or two about how WordPress works and am confident to make changes in the database without plugins, because I like to learn how things work. If I use a plugin, I must at least know what it will change. But this is me, not everybody has the time for this.
Please keep up your good work here, I like reading your blog.
Wow, Nile, what a comprehensive post and so many great tips.
I have bookmarked this for future reference!
Hope that my host has the repair option like you demonstrated in the MySQL.
Thank you so much for sharing your expertise!
Cheers,
Yorinda