When I started using WordPress, I changed themes every month and I tested a lot of plugins. I used to read those “top 21 must have plugins” articles and I just went and installed them all. Couple of weeks later, uninstall.
This is how you learn. You try, you test, you discover what you actually need, what works for you.
But years later you’re left with a huge WordPress database full of old, useless stuff because your website’s database doesn’t hold just your content but all themes and plugins settings.
Why it’s important to have a clean database
We had one customer, a food blog with ~10,000 pageviews/day, who was experiencing very slow loading time, 13-15 seconds. The obvious solution offered by the hosting company was to upgrade to a VPS from the shared plan she was on.
Our solution was different.
We discovered that WordPress was consuming 300 MB to generate a page. A normal WordPress instance should consume 30-40 MB, so you get an idea of how bad it was. No wonder it took 15 seconds to load and maxed out the resources allocated to her hosting account.
We discovered more than 25,000 database entries in wp_options table with autoload set to yes. “Autoload: yes” means that when WordPress is initializing it reads those options from the database. That alone took about 11 seconds.
We deleted those old, useless options and now WordPress consumes 50 MB and it loads in 2-3 seconds. We also optimized images, switched her to Genesis Framework, replaced some bad plugins with better ones and other small tweaks.
She didn’t need to upgrade to a VPS, she actually stayed on a shared plan for another 8-9 months when her traffic got to ~60,000 pageviews/day and it was really time to move away from shared hosting.
In conclusion, having a clean WordPress database helped her save money on hosting. And it can help you too so let’s see what you can do to optimize your WordPress database.
You can also export your database from phpMyAdmin, your hosting provider should offer access to this database management tool (or a similar one).
One of the basic things you can do is to delete unused plugins & themes, delete spam comments, delete trashed comments, trashed posts & trashed pages.
Delete unused plugins & themes
This also helps from a security point of view, I recommend you don’t keep on your server PHP scripts (plugins, themes, other PHP files) you don’t actually use.
To delete unused plugins login to wp-admin and go to Plugins > Installed plugins. Click on Inactive to see inactive plugins and delete them.
To delete unused themes go to Appearance > Themes, click on each theme you want to delete and then click Delete on lower right corner.
I recommend you keep only your active theme and one default theme (if you need to test incompatibilities). I use Genesis Framework so I only keep Genesis (parent theme), the Genesis child theme (active theme) and one WordPress default theme (for testing purposes).
Empty the spam comments
Login to wp-admin and go to Comments. Click on Spam to see the spam comments and use the Empty Spam button to delete them.
Empty the trash
While you’re still in the Comments section, click on Trash to see the trashed comments and use the Empty Trash button to delete them.
Go to Posts > All posts, click on Trash to see the trashed posts and use the Empty Trash button to delete them.
Go to Pages > All pages, click on Trash to see the trashed pages and use the Empty Trash button to delete them.
If you have any trashed custom post types you can delete them the same way. If you have Categories or Tags you’re not using you can delete those too.
You can also do this with the WP Sweep plugin, as explained below.
Use a plugin to clean the WordPress database
The easiest thing to do, especially for non technical people, is to use a WordPress plugin to clean up the database.
The most popular database optimization plugins are:
We recommend WP-Sweep because it uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries. The WP-Optimize plugin uses direct delete SQL queries which can leave orphaned data behind.
Install and activate the WP Sweep plugin then go to Tools > Sweep. You will see different sections with a Sweep button next to them, click the button to clean the clutter.
This plugin will help you delete:
- Auto drafts
- Deleted, unapproved & spammed comments
- Orphaned & duplicated post meta
- Orphaned & duplicated comment meta
- Orphaned & duplicated user meta
- Orphan term relationships
- Unused terms
- Transient options
The plugin can also optimize tables and it’s easier than doing it via phpMyAdmin. You can do this periodically, optimizing database tables, maybe once in a couple of months.
Sometimes, if you’re on shared hosting, WP Sweep can freeze because of PHP limitations. It happens particularly when it has a large number of database entries to delete, like removing transients.
If you can’t use the plugin to clean up your database you need to do the cleaning by running queries directly into your database. For example, the query to remove transients is:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');
Note: make sure to change the default prefix (wp_) to the one your database is using.
I use Sequel Pro and I prefer it to phpMyAdmin because it’s faster and it seems more stable, phpMyAdmin can crash sometimes (shared hosting, PHP limitations). I have never used MySQL Workbench to compare.
Whatever tool you choose, do not forget to backup your database first.
From my experience, one of the biggest problems with the WordPress database is a large number of database entries in wp_options table with autoload: yes.
You can find & delete those with a plugin – Clean Options – or directly in the wp_options table using phpMyAdmin or Sequel Pro.
Here is a good tutorial on using the Clean Options plugin but cleaning those options is a tedious task and no matter how you do it, there is no easy way, unfortunately.
Because the plugin shows you the “possibly orphaned options” you must be careful not to delete something that is actually used.
Basically, what you need to do is identify the options that are useless, like from old plugins you don’t have anymore. You would need to know what prefix each plugin has; such as, if you see many options with the “wcj_” prefix that’s from the Booster for WooCommerce plugin. If you don’t use Booster anymore it’s safe to delete them.
I don’t use the Clean Options plugin, I like to work directly on the database with Sequel Pro and I’m approaching this like I did in 1998 playing Tomb Raider, saving the game after every jump. I backup the table, search for options, delete options, check for errors, backup again, repeat.
Now, you have yourself a clean WordPress database. Keep it clean using the following best practices.
Best practices to keep your WordPress database clean
In no particular order…
1. Keep the revisions from piling up and limit the number of revisions that are stored in the database, 3 for example. Add the following code to the wp-config.php file.
define( 'WP_POST_REVISIONS', 3 );
2. Empty trash regularly or set it to empty automatically after a number of days, 5 for example. Add the following code to the wp-config.php file.
define( 'EMPTY_TRASH_DAYS', 5 );
3. Empty spam regularly.
4. Don’t leave comments unapproved – approve them, spam them or trash them.
5. Use WP Sweep plugin to optimize tables and check transients periodically (and sweep them, if necessary).
6. Beware of plugins that store a lot of data:
- statistics plugins
- security plugins
- anti-spam plugins
- related posts plugins
- link tracking plugins
I’m not saying you shouldn’t use any of the above plugins but use plugins that offer those functionalities and save data externally, not in your WordPress database. If you can avoid it, don’t use plugins that add bloat, keep the WordPress database as clean as possible.
7. If you delete a plugin you are sure you no longer want to use, clean after that plugin.
Most plugins don’t clean after themselves so you need to delete the database tables the plugin has added and delete the plugin options from wp_options table.
This concludes our 4th step in the quest for a faster & more scalable WordPress website. Next, we get to the most important step, it really should be #1 but since it’s the hardest we left it last.