Almost all WordPress websites I have seen had bloated databases. I think this is pretty normal. Nobody was born an expert in building super efficient and optimized sites.
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. A couple of weeks later, uninstall.
This is how you learn. You try, you test, you discover what you need, what works for you.
But years later you’re left with a WordPress database full of old, useless stuff. That’s because your website’s database doesn’t hold just your content but all themes and plugins settings.
Why it’s essential to have a clean database?
We had one customer, a food blog with ~10,000 page views/day, who was experiencing slow loading times, 13-15 seconds. The 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 typical 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 the 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 stayed on a shared plan for another 8-9 months when her traffic got to ~60,000 page views/day, and it was 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.
Backup First
Before you start to do anything, make sure you backup your database. I use UpdraftPlus with Amazon S3 but there are many backup plugins/solutions for WordPress, like:
You can also export your database from phpMyAdmin; your hosting provider should offer access to this database management tool (or a similar one).
Basic Cleanup
One of the primary 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 use.
To remove unused plugins login to wp-admin and go to Plugins > Installed plugins. Click on Inactive to see inactive plugins and delete them.
To remove unused themes go to Appearance > Themes. Click on each theme you want to delete, and then click Delete in the 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 keep Genesis (parent theme), the Genesis child theme (active theme), and one WordPress default theme (for testing purposes).
Empty the spam comments
Log in 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. 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 eliminate 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:
- Revisions
- 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.
Advanced cleanup
Sometimes, if you’re on shared hosting, WP Sweep can freeze because of PHP limitations. It usually happens 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.
You can run this query in phpMyAdmin (a tool accessible from cPanel) or with a database management tool like Sequel Pro (Mac) or MySQL Workbench (Windows, Linux, Mac).
I use Sequel Pro, and I prefer it to phpMyAdmin because it’s faster and more stable, phpMyAdmin can sometimes crash (shared hosting, PHP limitations). I have never used MySQL Workbench to compare.
Do not forget to backup your database first
A great tutorial on how to run queries in phpMyAdmin is this one on wpmudev.com.
From my experience, one of the most significant problems with the WordPress database is a large number of database entries in the 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 you shouldn’t and break your site.
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 as I did in 1998 playing Tomb Raider, saving the game after every jump.
I back up the table, search for options, delete options, check for errors, back up 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 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 some 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 that 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 the wp_options table.
This concludes our 4th step in the quest for a faster & more scalable WordPress website.
Leave a Reply