How to Make Your WordPress Database Clean as a Whistle

Your WordPress database is like a filing cabinet for your website that stores all of your content, including posts, pages, comments, revisions and spam comments, as well as the settings for your themes and plugins. So if you’ve been using WordPress for a while, chances are your database is cluttered and filled with tables you no longer need.

This useless data leads to database bloat (I mean, do you really need to save the settings for themes you deleted years ago?), so cleaning up your database not only helps speed up your site so it loads faster, but can also clear up significant space in your database so it runs more efficiently.

With WordPress, there are a few different ways you can tackle optimizing your database. In this post, we’ll look at some mySQL queries you can use to clean up your database in phpMyAdmin as well as some great plugins that make the task even easier.

Note: Before making changes to your database, I highly recommend you backup your website first. Whether you’re making small changes to your site or big ones, having a backup of your site ready to restore will give you peace of mind if something goes wrong. A plugin like BlogVault makes backing up your site easy.

Optimizing Your WordPress Database with phpMyAdmin

There are several ways you can run SQL queries on your database, but if you have cPanel on your server the best and easiest option is phpMyAdmin.

To access phpMyAdmin, login to cPanel for your site and click “phpMyAdmin” in the “Databases” section.

Once you’re in phpMyAdmin, you’ll see your website’s databases listed on the left. Click on the one you want to clean up and then click the “SQL” tab.

In the image above, I’ve blurred the name of my database, but you get the idea – I’ve selected the first database for my site. (I have a few databases to optimize since I used to have Multisite installed on my server!)

The SQL section in phpMyAdmin is where you can enter SQL commands and then hit “Go” to run them.

It’s important to note that this article uses the default table prefix wp_, so make sure you change the prefixes in the SQL commands below match the ones used by your database.

Delete Old Plugin and Post Data

Let’s start with deleting leftover data from plugins you no longer have installed. The wp_postmeta table also happens to be where your post data is stored, so when you run this query you’re hitting two birds with one stone.

DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';

Don’t forget to replace META-KEY-NAME with the value you want to clear out.

Delete Post Revisions

Old post revisions quickly add up, especially if you have authors on your site who are constantly saving their work over many days. If you want to delete all of the post revisions in your database in on hit, run this query:

DELETE a,b,c
 FROM wp_posts a
 LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
 LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
 LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
 WHERE a.post_type = 'revision'
 AND d.taxonomy != 'link_category';

Thanks to Joseph Michael Ambrosio for this query, which removes all revisions without unintended data loss and accidentally deleting link relationships.

Delete Spam Comments

It’s a chore deleting spam comments in batches, let alone one-by-one, but you can remove them all in one go with this query:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete Unapproved Comments

Here’s a query for admins who are too lazy to check comments! Simply run this this query to bulk delete all unapproved comments.

DELETE from wp_comments WHERE comment_approved = '0';

Delete Unused Tags

It’s easy for tags to accumulate over time, especially if you add them to posts and then change your mind and delete them. Also, tags have fallen out of favour in recent years as many bloggers have stopped using them. This query will delete all tags that aren’t associated with any posts.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Delete Old Shortcodes

Like old plugin data, forgotten shortcodes often hang around in your database long after you’ve stopped using them, or deleted plugins they were associated with. Instead of editing posts and pages manually to remove shortcodes, run this query to remove all instances of a shortcodes on your site.

UPDATE wp_post SET post_content = replace(post_content, '[YOUR-SHORTCODE]', '' ) ;

Don’t forget to replace [YOUR-SHORTCODE] with the unused shortcode you wish to remove.

Delete Pingbacks and Trackbacks

Does anyone use pingbacks or trackbacks anymore? Use these two queries to remove data for both pingbacks and trackbacks from your site.

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

Make sure you’ve disabled pingbacks and trackbacks before running these queries.

Delete Transients

Transients provide a way to temporarily store cached data in the database by giving it a name and a timeframe after which it will expire (hence the name “transient”) and be deleted. Sometimes, transients set by WordPress and plugins can take up a lot of space in your database, by they can be safely removed using this query:

DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')

Optimizing Tables

While you’re already logged into phpMyAdmin, why not optimize your tables? In a few clicks, you can quickly optimize your tables yourself without having to install any plugins.

In phpMyAdmin, on the “Structure” tab and then click on the database you want to optimize. At the bottom of the list click “Check all”. In the dropbox box beside this option, select “Optimize table”.

phpMyAdmin will automatically start optimizing your table as soon as your select the option in the dropbox and will then display the message “Your SQL query has been executed successfully.”

Optimizing Your WordPress Database with Plugins

There are a few free and premium plugins available that can help clean up your WordPress database with minimal effort. After testing lots of different options, WP-Optimize and WP-Sweep, in my opinion, are the best in terms of ease of use and actually optimizing your database well.

WP-Optimize

With more than 600,000 active installs, WP-Optimize is the most popular database optimization plugin for WordPress. It’s super easy to use, simply click “Run optimization” next to the clean up options you want to run.

The “Table information” tab displays all of the tables in your database along with their size. The screen also tells you the total size of your database and how much total space the plugin can help you save.

Under the “Settings” tab, you can choose to schedule database optimization so it runs automatically every week, fortnight or month, and even log all changes for you.

Overall, it’s a really simple plugin to use. Just remember to backup your site before using it.

Discover WP-Optimize

WP-Sweep

WP-Sweep is quickly gaining a following (currently at 50,000 active installs) because it was developed by Lester Chan, a popular WordPress developer.

When you install the plugin, go to “Tools > Sweep” to access the settings. The plugin has an intuitive interface that displays a report of how much unnecessary data is in your database. It’s divided into different sections for posts, comments, users, terms, options and optimizing tables.

When you click “Sweep” beside an entry, the plugin gets to work optimizing your database for that entry. If you want to sweep your whole database, just click “Sweep All” at the bottom of the page.

Unlike WP-Optimize, WP-Sweet uses WordPress delete functions as much as possible to clean up your database instead of running direct delete MySQL queries, ensuring orphaned data isn’t left behind in your database. However, WP-Sweep doesn’t offer automated database optimization.

Discover WP-Sweep

WP Rocket

Did you know WP Rocket also includes database optimization? Just click on “Database” in the WP Rocket settings to access this easy-to-use feature.

The interface is super intuitive. At a glance you can see exactly how many tables are available to optimize. There are five different sections, including posts, comments, transients, and database cleanup, which allow you to clean up revisions, trashed posts, transients and more. There’s also a section for scheduling automatic database cleanups, which you can set to run daily, weekly or monthly.

In order to clean up your database, simply check the options you want to sweep and click “save and optimize”. WP Rocket will then get to working optimizing your database.

What I like about WP Rocket’s database optimization features is how quick and easy it is to use without the need to install another separate plugin. Plus, with automatic cleanup enabled, there’s no need keep checking back – WP Rocket keeps my database tidy for me.

Discover WP Rocket

Conclusion

I hope this article helps you optimize and speed up the performance of your database and WordPress site. While phpMyAdmin can be a bit overwhelming to use at first, running SQL queries is fairly straightforward, though plugins make it even easier to clean up your database. Just remember to always backup your site before making any changes to your database.