Are you struggling with slow site loading times?
Just a few extra seconds can make a big difference. For every additional second it takes your site to load, your page abandon rate rises. About half of your visitors will expect it to load in 2 seconds or less, according to KISSmetrics.
If it doesn’t, they’re hitting that back button.
Need an easy way to shave a few seconds off your load time?
If your WordPress site’s been around for longer than a few months, cleaning up your database could boost those load times and decrease your bounce rate.
Why Does Your WordPress Database Need Cleaning?
WordPress software, as you probably know, uses PHP and MySQL. PHP is a programming language that’s used in WordPress to access and display the information stored in a MySQL database.
For every WordPress installation, there’s a MySQL database behind it. Your MySQL database contains all the information about your WordPress website, from your website name to your post dates.
Your MySQL database organizes all that information into separate tables. There’s a table for your options and settings, a table for your comments, a table for your post content, etc. Every bit of data, like your blog title, author bio, and each commenter’s name, are stored in those tables in your database.
A fresh WordPress installation will have 11 tables. And sometimes when you install new themes or plugins they create new tables in your database to store the information they need.
If you’ve ever changed themes or uninstalled plugins, they may have left information lying around your database that’s just taking up space. Your database also contains a lot of old information you don’t need anymore, like every single revision or draft version of your posts. It also keeps all comments, including pingbacks and spam.
That’s why, as your WordPress website grows and ages, it accumulates a lot of information in your database. All that information in your database is not only taking up valuable space, but can slow down your website, because it takes longer for your server to find and retrieve the data it needs.
By cleaning up and optimizing your WordPress database, you can shave valuable seconds off your site loading time.
Plus, getting rid of all that extraneous data will give you more storage space, in case your hosting plan is very limited.
Now that you know why it’s important to clean up your WordPress database, we’ll get into how exactly to do that.
But first…
Backup Your Database!
Like I said, your database contains all the information needed to run your site.
That means that messing with your database can be dangerous. Delete just one wrong thing, and you could end up breaking your website completely.
That’s why it’s so important to make sure you have a working backup and know how to restore your site before you start messing with your database.
Check out our list of the best WordPress backup plugins, and be sure to backup your database before you move on to the next section.
Go backup your database now! Don’t worry, I’ll wait.
Do You Really Need a Plugin to Clean up Your Database?
It’s understandable that you might be a little leery of installing another plugin on your site, especially after I talked about how plugins often add more extra information to your database.
It’s definitely possible to clean up your database without a plugin, if you know what you’re doing.
If you log in to your account hosting account’s cPanel, there should be a tool there called PHPMyAdmin. Using PHPMyAdmin, you can directly view and manage all your MySQL databases.
What a MySQL database for WordPress looks like in PHPMyAdmin
Under the “Table” column is the name of each table in your database. The “size” column shows how much space that table takes up.
You might notice that your “wp_commentmeta” table is the largest. This is pretty common, since that’s where WordPress stores all your comment history, including spam and other data you just don’t need.
As you can see, PHPMyAdmin looks a little intimidating if you’re not familiar with managing databases. It can be hard to tell exactly what each table is for, and it’s easy to make an irreversible mistake. That “Drop” link, for example, will irreversibly delete that table and all the data stored in it.
If you did want to clean up your database using just PHPMyAdmin, you’d have to go table by table, identifying the specific data you want to delete, and entering MySQL commands to delete that data. Unless you’re very familiar with MySQL, I wouldn’t recommend trying this. It’s just too easy to delete the wrong thing.
(However, you CAN optimize your tables easily in PHPMyAdmin. Just click on the “Structure” tab, and check the checkboxes next to all the tables you want to optimize. Under the list of tables, you’ll see a dropdown box. Choose the option “Optimize Tables,” and your database will be optimized!)
That’s why I like using a plugin to clean up my database. Plugins can scan and evaluate your database and tell you what data is needed, and what can be deleted. Many of them will warn you about features or tools that can potentially break your site. They’re much more user-friendly.
And, if you’re concerned about a plugin taking up space, you don’t have to leave it installed. You can just let it do its thing, and then delete it until you need another cleaning.
Top 3 Plugins to Clean Your WordPress Database
There are many great WordPress database optimization plugins that you can use to keep your WordPress website squeaky clean. Let’s go over a few our the most popular options.
WP-Optimize
The WP-Optimize Dashboard
WP-Optimize is a highly-rated plugin: 4.7 out of 5 stars, with hundreds of reviews. In just one click you can use WP-Optimize to clean up and optimize your database. It can clean up post revisions, spam comments, and lots of other extraneous information. You can also set it to automatically clean your database once a week.
After installing, click on the new menu option “WP-Optimize.” There are no sub-menus; everything you can do is right there.
As you can see above, it’s pretty user-friendly even if you’re new to database management, since all the potentially dangerous options are marked in bright red with a warning.
If you click on the “TABLES” link at the top, you’ll see a list of your database’s tables, along with an analysis of how much space they’re using up. There’s also a total sum of your database space, and how much space you could save by optimizing.
Under “SETTINGS,” you can schedule automatic database cleanups to run weekly, biweekly, or monthly.
This plugin does NOT have a backup feature included, so please be sure to backup your database with another method before you install this plugin.
WP Clean Up Optimizer
WP Clean Up Optimizer cleans up old information from your database, and also has optimize and repair options. This plugin cleans up post revisions, drafts, comments, pingbacks, and orphaned information like post meta.
There’s also a Pro version with a lot more features and premium support. With the pro version You can also schedule cleanups to run automatically at any interval you choose, from daily to yearly.
To use WP Clean Up Optimizer, install the plugin and navigate to the new “WP Clean Up Optimizer” menu option. Right on the dashboard, you’ll see some items you can empty out of your database:
The WP Clean Up Optimizer Dashboard
You can then click the “DB Optimizer” tab in the Dashboard to optimize individual tables. Tables that are integral to WordPress’s function are highlighted in red to prevent you from unintentionally breaking your site.
Unfortunately, a lot of the features are disabled in the free version. But if the automatic scheduler and other features are worth it to you, you can pick up the premium version starting at a one-time fee of £8, or about $12.50.
WP DBManager
With just over a million downloads and rated 4 out of 5 stars, WP DBManager is one of the more popular database cleaning plugins.
WP DBManager has features to completely manage your WordPress database. Besides cleaning it, you can also backup, restore, repair, and optimize your database. You can also schedule an automatic backup/repair/optimize and email yourself the backup file.
After you install and activate this plugin, you’ll see a the new “Database” menu option. You can backup your site by clicking on Database > Backup DB.
After you backup your database, you can go to Database > Optimize and choose which tables you’d like to optimize.
There’s also a menu option called “Empty/Drop Tables.” Be very careful in this menu, and don’t use it unless your 100% sure of what you’re doing. In MySQL, to “drop” a table means to delete it forever. There’s a tiny warning at the bottom of the page, but it’s not very noticeable.
Under Database > DB Options, you can schedule automatic backup, optimization, and repair of your databases.
Automatic scheduling for your database maintenance
Backup, Then Get to Cleaning!
Don’t forget to backup your database before trying one of the plugins above, and let us know how it goes!
How do you keep your database clean and running smoothly? Give your suggestions below!
Just tell me one think that If I have installed 10 plugins but 8 of them are deactivated. Those 8 deactivated plugins affect my website speed or not.
Really useful information! I didn’t know that I can clean up the plug ins! Thanks so much for sharing! I have friends with word press blogs and I guess that they will be happy to learn that they can clean up their database!
For free version the WP Optimize plugin will take care about database optimization.
Anyone has been tried WP Sweep Plugin for optimize database?
It’s new plugin in early 2015.
“the main distinguishing characteristic of WP-Sweep
is that it uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries. Whereas the WP-Optimize plugin uses direct delete SQL queries which can leave orphaned data left behind.”
Thank you so much for this post.
Few days ago i have got an error 503
Than i call my hosting provider for the solution. they told me that its happen due to database stack overflow. And i need to cleanup my database.
Well its your article which bring my website back.
Thank you again
Thank you so much!! Just the answer I was looking for
What I want to know is whether you create tables for your themes or use the Options table to dump your stuff
superb article..! 🙂
tried the wp optimizer, it was able to shrink database into considerable size, thus making the site loading queries less.
I keep meaning to give WP-Optimize a go but I hate the idea of messing with the database. It is easy and best for me. Thanks.
Hello! thank for your post!! I have a doubt after installing Wp CLean Up Optimizer: if I empty the revisions, does he delete even the drafts? Thanks!! 🙂
You’re welcome, Lori! From the plugin description it does say it removes post drafts as well: https://wordpress.org/plugins/wp-clean-up-optimizer/
oh! thanks a lot for your precious reply! I wish you a Merry Christmas!! byebye, lori
Really usefull post.
Thanks,
Sonia
Thank you, Sonia! I’m glad you found it useful 🙂
If you want to clean everything, look up some of the other plugins that take out database tables and cruft left behind by plugins you uninstalled.Take a look at WP’s cron too; there are a couple plugins that let you sort out the junk left behind by plugins there, or identify slowdowns caused by problems with cron events.
And yes take backups before you mess with these things or have someone else take care of it who knows what they are doing.
What plugin will remove tables left over from uninstalled plugins? The only one I know about is WPDBSpringClean, and it doesn’t work with the newest WordPress versions.
Bump
Good advice, Dan! I wish more plugins would clean up after themselves when you uninstall them.
I think WP-Optimize plugin best 🙂 I love that plugin.thanks for your useful tips
You’re welcome, Noman! Glad it helped 🙂
WP Optimizer is a Good Plugin, Can we ask our hosting to clear those database ?
Thank you for the article.
Hi Sri, if you have a managed hosting plan they may do it for you, but I think with most hosting plans they probably wouldn’t, because it’s not included in the provided services, plus I’m sure they wouldn’t want to get blamed if something went wrong 🙂
But it’s pretty easy to do yourself! Try one of the plugins listed above, and let us know how it goes!
I’ve got one WP site where there are around 500 pages of content and the dB is way bigger than any of my other sites. I’m going to look into some form of optimization but since there are 500K users a year on the production site… I think I’ll backup the entire site and put it up on a test URL to play with tuning that dB.
Sounds like a good plan, Mal! Good luck – let us know how it goes 🙂
Super interesting
I just logged in to my MySQL database and found the wp_options table to be 347MB. Deleted _transient_ type entries, optimised the table and now it’s only 1.5Mb.
My website runs much quicker now!
Wow! Awesome that it worked so well for you, anthony 😀
Hi KeriLynn
I keep meaning to give WP-Optimize a go but I hate the idea of messing with the database.
This post might just be the one to give me a little prod to give it a try… after I’ve backed up the database of course.
I know the feeling, Keith! As long as you have a working backup and know how to use it, you’ll be fine even if something should go wrong.
I’m the same way, but I felt better after practicing restoring a backup on another smaller site, just so I’d know exactly what to do just in case. It might help you to try the same thing before you clean & optimize the database of your own site. You could even set up a mirror WordPress site on a subdomain just to practice and test things like that.
Would you recommend using one of these plugins occasionally even if you are running ManageWP? or would it be doing the exact same thing?
referencing the blog post here on Elegant Themes from last week: http://www.elegantthemes.com/blog/resources/managewp
Good question, Loralee. ManageWP has a database optimization feature available under the Overview widget of the Dashboard.
However, it looks like it’s just optimizing the database, but not necessarily cleaning it by deleting unneeded files. So you might find an additional plugin to be useful once in a while, but you may want to check with ManageWP support beforehand just to be sure there won’t be conflicts.
Really helpful—thank you so much!
You’re welcome, Mark!
I’ve been using Optimize Database after Deleting Revisions. Also can be set to run automatically.
Any idea how this compares to the others mentioned? Saves me from logging in to the phyMyAdmin and doing things manually.
Hm, I actually hadn’t heard of that plugin, Mike, so thanks for sharing it! I’d say as long as you’re getting the results you want, no need to switch.
I use the plugin “Optimize Database after Deleting Revisions” plugin, also. Another benefit I’ve seen is the refresh of screens seems faster when editing and saving if you’ve cleaned out the database. Not sure why that might occur. In fact, I didn’t realize until later that wasn’t the plugins main purpose. You can schedule how often to “purge” the database. Even better.
Thanks for writing this article.
Awesome, I’m going to have to give that plugin a try. You’re very welcome 😀
Great post, thanks!
Although I believe in keeping a tidy folder hierarchy and clean database I must say I have never actually noticed any differencr between a clean or cluttered database. Even with traffic/comment-heavy sites it always seems that the main difference in website performance is the hosting quality.
Absolutely true that hosting quality has a huge effect on performance! Cleaning and optimizing your database won’t help much if you’re sharing hosting on an overcrowded server.
Hi KeriLynn, thanks for the article. I didn’t know it was possible to do a clean up with the plugins. Useful info.
Thanks.
Elijah.
You’re welcome, Elijah! Happy it helped 🙂
Don’t play with dangerous toys if you have huge WP website (ie. 30k posts, 20k comments, WP DB tables 500MB etc.) since they will make your life miserable – especially WP-Optimize which I’ve tested – destroyed taxonomy completely.
Use phpMyAdmin optimize feature, and for all other stuff it is ‘cheaper’ to hire someone. And never ever do anything with your DB if you didn’t create backup!
Excellent advice, Vladimir! It’s often true that hiring an expert saves you money in the long run. Even if you know what you’re doing (or just get lucky!), your time can often be more wisely spent on other things.
Thank you so much KariLynn for writing on this. WordPress security and optimization have been on my mind a lot lately, and this came at just the right time!
I’ve never used any of these plugins before, I’ll have to look into ’em.
Awesome, I love serendipitous timing like that 🙂 Good luck!
We utilize WP-Optimize to keep our databases minimized. There are also optimizations you can do to mysql itself if you are using it on a dedicated server and have access to my.cnf
Good point, Arash! I’m no expert & I’m a little paranoid about messing with MySQL directly, so I prefer a plugin myself. But I admire those who can do it themselves!
I use free version WP Optimize on every site. Is time to change a PRO.
I use WP-Optimize in all my websites. I only activate when using
Does wp optimize get rid of breadcrumbs left by unused or old plugins?
WP Optimize may not get rid of *everything* that’s unused in your database… I don’t think WP Optimize will delete tables, so if a plugin creates a new table and leaves that table behind when it’s uninstalled, WP Optimize will not automatically delete the orphaned table for you.
It does have a feature to scan all your tables and show which database tables have overhead and wasted spaces.
There’s another plugin not included on this list called WPDBSpringClean that can identify unused tables and give you the option to delete them: https://wordpress.org/plugins/wpdbspringclean/
WP-Optimize is my favorite, too, Irena!
thanks the useful post 🙂
You’re welcome, Pedro!