How to Cleanup a Bloated WordPress Database (With Examples)

Over time, and after switching between dozens of plugins and themes, your WordPress database can accumulate tons of clutter and grow in size.

While a large database does not necessarily mean a slow site, this clutter can be a burden to carry in many ways.

What’s worse, most WordPress users would never dare tread inside their site’s database in fear of removing the wrong tables, or breaking their site.

In this video and following guide, Alex attempts to dispel the fear of working in your database by walking you through what to look for and how to write easy to use queries to quickly clean out your database and potentially remove thousands of unused rows.

Database Queries From the Video

The following queries can be run from phpMyAdmin to perform bulk queries in an automated manner. To run any of the following queries, simply paste them into the SQL panel.

It’s wise to first “simulate” the query before running it so you can check which tables are included in the deletion, and that the query is valid.

How to run SQL queries from phpMyAdmin

Simulate the query before actually running it.

1) Bulk Delete Old MD Tables From wp_postmeta

Skip this if you started using Marketers Delight after version 4.7, or are using a different theme.

If you upgraded your site to Marketers Delight 4.7 from an older version, chances are there are still old MD postmeta keys in your database.

Run the following queries in the SQL panel to clean out any old and now obsolete meta keys:

DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%video_lead_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%action_lead_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%funnel_lead_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%table_lead_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%email_lead_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '%md_%';

2) Bulk Delete Postmeta Tables By Prefix

DELETE FROM `wp_postmeta` WHERE meta_key LIKE 'prefix_%';

Make sure you target the correct database table in your query, otherwise no operation will be run (see wp_postmeta in red). You can be sure the table name is correct as it will be listed in the phpMyAdmin table list.

Next, you will need to match the second red highlight with the prefixes you wish to bulk delete. The % in the query is wild card, and matches and string after the selected prefix.

I explain how to find both of these out in the video, and the prefix of the table rows you find often match the name of the plugin or theme you’re targeting.

3) Bulk Delete Old Post Types From wp_posts

DELETE FROM `wp_posts` WHERE `post_type` = 'gallery';

Now we’re targeting the wp_posts table to delete any data from plugins that created post types in the past. I’ve found the best way to do this is to search the wp_posts table rows by the post meta name so you can easily recognize which are still in use or not.

4) Delete Old Yoast SEO Cache Validator Options From wp_options

DELETE FROM `wp_options` WHERE option_name RLIKE 'wpseo_sitemap_[0-9]*_cache_validator';

Credit to @dgola on GitHub

If you use the Yoast SEO plugin, chances are the plugin in the past or present has created a bunch of options in the wp_options table for clearing the plugin’s cache.

These options are simply not needed and, because wp_options is so vital to how your page loads, can actually slow your site down if this table gets too bloated.

The query above will delete these options by searching for the Yoast prefix in the options table, then any following combinations of numbers 0-9 in between; so you can be assured this query is highly targeted to only delete what we intend to.

How Much Database Space Did You Save?

By investigating my own database here on the MD site, I was able to reduce my database size by over 60%! Couple that with the lightning fast new MD4.7, and my site has never ran faster!

After cleaning out your database, how much space did you save, and were you able to remove thousands of old and unused records? Let me know in the comments below!