How to clean up your WordPress database

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 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.

blog-traffic

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 just 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 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 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.

Whatever tool you choose, do not forget to backup your database first.

You can find a complete list of useful queries on CatsWhoCode. Another 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.

11 responses to “How to clean up your WordPress database”

  1. Collins Agbonghama Avatar

    Amazing resource on cleaning WP database. Thanks for sharing this.

    1. Andrei Chira Avatar

      Thank you. If you need any other info or tutorials we could write, please let us know.

  2. Hong Ng Avatar

    Have you tried TablePlus? It’s similar to Sequel Pro but it supports multiple drivers.

    1. Andrei Chira Avatar

      Never heard of it until now.

  3. Mathukutty P. V. Avatar

    I am using Akismet. Should I remove that also? I tried UPDRATPLUS free version several times, but always memory exhaust and site down. Increased memory in php.ini and tried, but no success. I have Godaddy shared Linux hosting.

    1. Andrei Chira Avatar

      I prefer Antispam Bee, I think it’s more effective in fighting spam than Akismet.
      Most backup plugins have issues on shared hosting, a solution that might work better is to use ManageWP backup or WP Time Capsule backup.
      These solutions make incremental backups, so there’s no compressing and zipping, and that uses fewer server resources.

  4. Marie Avatar

    With the help of the knowledge obtained from your article to clean up the database, it turned out to be pretty easy and without any mistakes or problems.

  5. priya Avatar

    These is a really great article, thank you for sharing and i would like to say that, please keep sharing your information for us.

  6. CityBreak.Club Avatar

    Thank you.
    Works like a charm!

  7. K Store Avatar

    Very good information shared, thanks for this.

  8. Bigg Nutrition Avatar

    Very good article shared, thanks for this.

Leave a Reply

Your email address will not be published.