Table of Contents

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. Plus, if you want to fix a slow WordPress admin, cleaning your database can make a difference.

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.


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

WP Rocket Database Optimization: Posts and Comments

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.

WP Rocket Database Optimization: Transients, Database, Automatic Cleanup

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!


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.

Comments (12)

I was wondering why my website has gone much slower compared to previous years. I tried using Wp-Optimize to clean up my database. It was very simple and easy to use and I cant say it has increased the speed of my website by 2x. Thank you for this informative post

Hi Stephen! What has changed on your site compared to previous years? You might want to do a review of your content, plugins, web host etc so you can narrow down the cause. Good luck!

Hi, Raelene

I can't express my feeling with few words, but I just want to tell you, this article just saved my life today.

My website's database was got bigger to 1455 MB with only 55 articles, and I don't know how did this happen. Maybe I experiment a lot, like testing plugins and updating post often.

But, when my website crashed I followed this tutorial and the database size decreased to 848 MB. I can't tell you how happy I am today.

Thank you very very much!
Nootan Kumar

@Nootan_Kumar Oh wow, so glad the article helped you! That's awesome :)

Hi, Raelene
after reading this article you wrote, i am very glad to read the practical handicraft technology, but among them
"update wp_post set post_content = replace .."
maybe that was true a few years ago, but now it's missing an "s" in wp_posts
i have also been troubled by database problems for a long time, which is one of the directions i explore. thank you for the guidance of this article.

Hi, Raelene
New Update! My database size has been reduced very heavily this time. I was still doing some mistakes, after running proper commands, it has been shrunk to 14.5MB (previously was 848MB).
Thanks again for this wonderful guide!

Thanks for this. How to do this via WP CLi?

Great article, but I am left with a question: How do I know which tables were used by plugins I have deleted?

In my case, the tables I really want to clear out are from the "WP eStore" plugin.

As I use to clean up database and transients, in the past with wp-optimize, but lately with the built in tool from wprocket, I still wonder what to do in case this action breaks the site or makes it inaccessible.

Yes, I always make a full backup or at least database backup (using BackupBuddy for years) - however I wonder what I should do in that case...
my question is - did this happen to anyone and what would be, in your opinion and experience, the fastest way to restore everything as it was before the optimization?

Actually I have a well running shop page with over 800 user accounts, 150 products and variations and an average order count of 20 per day...

thanks in advance

Thanks for sharing. This really helped me clean up my post meta. From over 50,000 to less than 30000 tables.

Thank you so much for this interesting tips. I hope that you will continue sharing your experience in WordPress. How do you think, is it possible to move my website from ASP NET to WordPress?

Latest Articles of Page speed and caching
Subscribe to Our Newsletter

Stay in the loop with the latest WordPress and web performance updates.
Straight to your inbox every two weeks.

Get a Faster Website
in a Few Clicks

Setup Takes 3 Minutes Flat
Get WP Rocket Now