How to Do a WordPress Database Cleanup

Ever noticed your sleek sports car beginning to drag after a season or two, demanding a tune-up? Similarly, your WordPress website needs periodic under-the-hood tweaking to keep it running at peak performance. Today, we dive into how to do a WordPress database cleanup, a critical yet often overlooked aspect of website maintenance that ensures your digital presence remains fast and efficient.

In this guide, I’ll walk you through the necessary steps to optimize your database, clear out the cobwebs of redundant data, and inject new vitality into your site’s operations. By the article’s end, you’ll have mastered the skills to conduct a WP database sweep, optimize WordPress database settings, manage post revisions, and ensure your database’s security is iron-clad.

Prepare to transform your WordPress site with strategic clean-up maneuvers that promise to boost your site’s speed and improve overall functionality. Here’s how we’ll achieve that:

  1. Understanding the Basics of WordPress Databases
  2. Step-by-Step Guide to Database Optimization
  3. Tools and Plugins that Make the Job Easier
  4. Maintaining Your Optimized Database for Long-Term Success

Empowering your website starts here – let’s get cleaning!

What Causes the Database to Expand

During a website’s lifetime, the database grows to unexpected sizes. The programming of WordPress records a lot of information.

A large amount of unnecessary information causes the database to become heavy. The larger the database grows the slower the website becomes.

It also puts a larger burden on the server setup. There are further hidden causes for the database cluttering so fast.

Problems from Plugins

Even when it is no longer in use, the database retains plugin information.

To remove all the plugin’s information it is necessary to delete the plugin entirely. Deleting the plugin will save space, but it will not immediately free up space in the database.

For most plugins, even after deletion, the settings remain stored in the database.  So if the user reinstalls the plugin it is immediately ready to use, with all the usual settings.

However, it also leaves a lot of orphaned data filling up the database. One of the places where deleted plugins leave information is the options table.

Unnecessary Comments

Receiving comments is great. It shows that people are visiting the website and expressing their appreciation.

But some comments contain spam or inappropriate content. Even with filtering, these still end up in the unexplored folders of the database.

It takes some effort to delete spam, comment meta, and trashed comments, but it is not difficult. Find out below how to do it fast.

Database problems from Transients

Transients are similar to options. The difference is that transients have an expiry date and options do not.

Transients are a method of server-side caching and they store information from third-party sites.

After reaching their expiry dates, transients are usually deleted. So in the long run, they do not cause any database-cluttering problems.

But many transients last longer than is necessary and do take up space. In this instance, they are a contributing factor to the database clutter. A good cleaning will get rid of them.

WordPress Post Revisions

WordPress has an auto-save function. This feature makes various copies of a post while it is being written.

This is useful if a mistake is made, or when different authors contribute to a blog. Most of the time it is not necessary and it leaves many duplicates of a single in the database.

There are two ways to limit the number of saved copies in the database.

One is to use revision control. With this, the user can limit the number of revisions that WordPress stores.

A second way is for the user to periodically clean the database. It is advisable to use both approaches.

Besides cleaning, optimizing also helps to save a lot of database space.

Old Posts

WordPress saves every single post that was ever posted on a page or blog. It is nice to read some of these older stories, but they do take up valuable space.

If they are important, it is best to save them on a local drive. Read how to remove them later on in this article.

Large Log Files

Every plugin maintains a log in the database. So the more plugins a user employs, the more space the logs will take.

For example, after using the Redirection plugin for two weeks, it will have stored more than 8000 log pages. This can amount to a doubling of the database size.

Cleaning a WordPress Database with phpMyAdmin

There are many approaches to cleaning a WordPress database. The most direct approach is using an SQL query.

This method requires some background knowledge and is only recommended for advanced users.

There are different ways to run an SQL query. The easiest and most practical way is using phpMyAdmin.

This requires that the server uses cPanel.

Access phpMyAdmin by logging in to cPanel and click phpMyAdmin under Databases.

phpMyAdmin lists the website’s databases on the left. Click on the database to clean up and then click the SQL tab.

In this section, enter the desired SQL commands and then hit Go to run them.

In the continuation of this article, the default prefix wp_ is used. Make sure to replace this prefix in the SQL commands with the one that your particular database uses.

Unused Plugins and Themes

Deleting unused themes and plugins frees up space and also improves security. It is best to remove any unused PHP scripts from the server, such as themes, plugins, and other PHP files.

To see the unused plugins, go to wp-admin and select Installed plugins under Plugins. Then click on Inactive to filter out the ones that are not in use.

Select all the inactive plugins and delete them.

To delete unused themes, go to Appearance, then to Themes. Select all obsolete themes and click Delete in the bottom right corner.

Strange Characters

Encoding problems are a big issue. Some update all their posts by hand.

The query below will automate the removal of strange characters from the database. It slims down the database and the posts will also be more pleasant to read.

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘“’, ‘“’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘‒, ‘”’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘’’, ‘’’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘‘’, ‘‘’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘—’, ‘–’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘–’, ‘—’);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘•’, ‘-‘);

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘…’, ‘…’);


UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘“’, ‘“’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘‒, ‘”’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘’’, ‘’’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘‘’, ‘‘’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘—’, ‘–’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘–’, ‘—’);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘•’, ‘-‘);

UPDATE wp_comments SET comment_content = REPLACE(comment_content, ‘…’, ‘…’);

Post Revisions

WordPress saves drafts of posts, so they are retrievable if something goes wrong. Over time, all these saved and unused revisions take up a lot of database space.

The query below will remove all revisions and their associated metadata.

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) WHERE a.post_type = ‘revision’;

Spam Comments

It is possible to Remove spam comments one by one, but that is a lot of work.

To make life a little easier, there is also a batch removing feature. That is still a tedious job.

The following query will remove them all in one go.

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

Reset Administrator Password

Security is very important, even more so for a WordPress website. To ensure that the site continues to be secure, change the password at regular intervals.

Because passwords are also saved in the database, a simple SQL query will help to change the password. Below is a query to do that.

Replace admin_username and new_password with the username and password that is changing.

UPDATE `wp_users` SET `user_pass` = MD5( ‘new_password’ ) WHERE `wp_users`.`user_login` = “admin_username”;

The MD5 function will create an MD5 hash of the password. This is the common security practice for WordPress passwords.

Unapproved Comments

Checking every single comment to see whether it is approved or not is way too much work for any website owner.

This query is for any administrator who wants a simpler way to do this. It bulk deletes unapproved comments.

DELETE from wp_comments WHERE comment_approved = ‘0’;

Unused Tags

Tags can accumulate fast over time. Administrators often use them for a short time and then delete them.

Tags are also not as popular as they used to be. Many bloggers have stopped using them altogether.

The following query deletes all unassociated tags.

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

Update Links to HTTPS

Using HTTPS will add to the safety of a WordPress website. This will make it necessary to update all the hardcoded links in all the articles to HTTPS.

Checking every article for links and manually updating them is a lot of work. To help, below is a query that will automate this task.

It takes less than one minute to type and execute it. Do not forget to replace with the URL of your website.

UPDATE wp_posts SET post_content = replace(post_content, ‘’, ‘’);

Hey, did you know data can be beautiful too?

wpDataTables can make it that way. There’s a good reason why it’s the #1 WordPress plugin for creating responsive tables and charts.

An actual example of wpDataTables in the wild

And it’s really easy to do something like this:

  1. You provide the table data
  2. Configure and customize it
  3. Publish it in a post or page

And it’s not just pretty, but also practical. You can make large tables with up to millions of rows, or you can use advanced filters and search, or you can go wild and make it editable.

“Yeah, but I just like Excel too much and there’s nothing like that on websites”. Yeah, there is. You can use conditional formatting like in Excel or Google Sheets.

Did I tell you you can create charts too with your data? And that’s only a small part. There are lots of other features for you.

Old Shortcodes

WordPress shortcodes obviate the need to modify WordPress themes. By using them, embedding information in an article is much faster and easier.

There are many plugins available that make integrating data in the WordPress editor this simple.

There is also a downside to the use of shortcodes. Unused ones can create readability problems.

This is something that often becomes apparent when switching WordPress themes. Then shortcodes start appearing in the text of the page or post.

This SQL query will remove them. Be sure to change [tweet] for the shortcode that should be removed.

UPDATE wp_post SET post_content = replace(post_content, ‘[tweet]’, ” ) ;

Pingbacks and Spam Comments

To remove spam comments in batches use the following query. This one will also remove pingbacks.

DELETE FROM wp_comments WHERE comment_approved = ‘spam’ OR comment_type = ‘pingback’;

The query below also removes metadata for removed comments.

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);


Transients are information that is temporarily stored in the database. WordPress uses them to store cached data by giving it a name and expiry date.

After the expiry data WordPress will delete them. Still, they can take up a lot of space.

The query below will safely remove them.

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

Unused Tables and Unused Space Between Tables

Uninstalled plugins can leave behind unused tables. These also take up database space.

WPDBSpringClean is a plugin that removes these unused tables. This will leave the database clean and tidy.

Optimizing the Database with Plugins

Using queries takes a little knowledge and experience. For administrators with limited technical background, there are plugins for database clean up.

Some of these database cleaners are free and some have a price tag. All of them maintain the database with minimal effort.

Below is a list of the most popular ones.


WP-Optimize is one of the most popular choices. It is well-equipped and it is even compatible with mobile devices.

Another interesting feature is the automatic back-ups with UpdraftPlus.

It removes:

  • Auto drafts
  • Pingbacks
  • Post revisions
  • Spam comments
  • Trackbacks
  • Transients
  • Unused tables

It will make the website and admin faster.


WP-Sweep is on the rise. Instead of relying on MySQL queries, it uses the WordPress delete functions.

The coding is superior because of this simple approach and is more practical to work with. It ensures that no unused or orphaned data remains behind.

WP-Sweep does not offer an automated optimization service.

Advanced Database Cleaner

This WordPress Database Cleanup plugin is like WP-Optimize. This plugin allows the user to do a more thorough job and includes cron jobs.

It removes all unnecessary data like old revisions and trashed comments.

There is a premium version with added features. This version removes old plugins and offers extra options for cleaning themes, orphans, and WordPress.

The paid version also has an option for scheduled cleanups.

Optimizing Database after Deleting Revisions

The popularity and ratings of this cleaner are exceptional.

It deletes unused data and obsolete information. This includes the likes of pingbacks, spam comments, tags, and trackbacks.

On top of these functionalities, it also deals with the oEmbed cache. WordPress uses this cache to read embedded code from platforms like YouTube.

Some of these embedded codes are damaged and therefore useless. This plugin will delete them.

Smart Cleanup Tools

Cleanup Tools is not available as a free version. However, the price of $30 is reasonable considering the features that it packs.

A WordPress cleanup becomes an easy and quick task with this tool. The special features include:

  • A WordPress Toolbar Menu with cleanup options and quick access
  • AJAX for its cleanup operations
  • Decluttering of all the statistical data
  • Keeps a log of all the SQL queries during the cleanup check and execution

WP Reset

This advanced cleaner is the most versatile WordPress plugin on the list. It presents a set of tools that will be of benefit to both non-developers and developers.

It can reset a WordPress installation to its original settings without removing any files. But it also has a feature for deleting obsolete data as well.

An interesting feature is the Database Snapshot tool. With this, the user can see what changes WP Reset will make when it performs a cleanup.

With features such as webhooks integration, WP Reset helps manage or reset a database.


WP-DBManager WordPress database cleanup tool is a simpler option and contains fewer features. Yet, it is very efficient in optimizing a WordPress database.

It repairs, backs up, cleans, and optimizes a database fast and easy. The user can even employ WP-DBManager to run queries.

To ensure continued optimization users can implement the automatic scheduling function.

FAQ about doing a WordPress database cleanup

Why is a WordPress database cleanup necessary?

A swift, responsive website is the lifeblood of your digital presence. Over time, your WordPress site accumulates unneeded data such as spam comments and post revisions, which bog down performance. Regular database cleanups keep your site running smoothly and improve loading times, significantly enhancing user experience and SEO rankings.

What tools are best for WordPress database cleanup?

Explore the realm of plugins! WP-Optimize, Advanced Database Cleaner, and WP-Sweep stand out by offering user-friendly interfaces and robust features for optimizing SQL queries and clearing out redundant data. These tools handle intricate tasks like removing spam comments and managing post revisions with ease.

How often should I clean my WordPress database?

Seasonal maintenance works wonders. Tidying up your WordPress database quarterly ensures it remains sprightly and efficient. If your website sees heavy traffic or frequent updates, consider a bi-monthly schedule to keep everything in prime condition and reduce the server load, maintaining an optimal balance.

Can a WordPress database cleanup break my site?

It’s unlikely, but caution is key. Always back up your WordPress database before initiating a cleanup. Using reliable tools and plugins minimizes risks. If unsure, it’s wise to consult or hire a professional who ensures the process is handled smoothly without affecting your live site.

What specific data should I focus on during a cleanup?

Target the main culprits of clutter. Post revisions, spam comments, transient options, and unused tags significantly slow down database operations. Focusing on these areas helps streamline your database efficiently. For a thorough cleanup, also consider removing orphaned metadata and unused plugin tables.

How does database cleanup impact website speed?

Cleaning accelerates performance. By removing overhead and optimizing the database structures, such as clearing out old “transient options,” your WordPress site can retrieve data faster. This not only speeds up your backend operations but also enhances the front-end user experience, potentially boosting your SEO efforts.

Are there any risks associated with WordPress database optimization?

The main risk involves data loss. Incorrect handling during the cleanup process can lead to crucial data being deleted. Always ensure you use reputable plugins and conduct a thorough backup. By doing so, you safeguard your data while enhancing your website’s performance and security.

What’s the best way to back up my WordPress database before cleaning?

Reliable tools are your best allies. Plugins like UpdraftPlus or BackupBuddy offer comprehensive solutions that not only back up your WordPress database but also your entire site. These backups can be scheduled and stored off-site, ensuring you have a recent copy before any database manipulations begin.

Should I hire a professional for my WordPress database cleanup?

Consider your comfort with technology. If the thought of delving into databases and handling potentially site-breaking operations invigorates you, give it a go yourself with assistance from trusted plugins. However, hiring a professional provides peace of mind with expert precision, especially beneficial for large or complex sites.

What aftercare is recommended post-WordPress database cleanup?

Regular checks keep your site in top shape. Post-cleanup, monitor your website’s performance and functionality to ensure everything runs as expected. Setting up regular automated backups and periodic cleanups will maintain your database’s health long-term, preventing the gradual decrease in performance and ensuring ongoing efficiency.


Embarking on how to do a WordPress database cleanup isn’t just about sprucing up; it’s a vital investment in the health and speed of your site. By now, you’ve navigated through the step-by-step processes, from identifying redundant data like spam comments and outdated post revisions to optimizing SQL queries for enhanced performance.

Each stride taken in cleaning your database not only secures its foundation but also revitalizes your site’s dynamics, ensuring a seamless user experience and boosting your SEO efforts. Remember:

  • Regular backups are crucial before each cleanup.
  • Utilize trusted plugins like WP-Optimize or Advanced Database Cleaner for efficiency.
  • Schedule periodic cleanups to maintain consistency in performance.

Moving forward, maintain this regimen to keep your site at its peak. Your website is a living entity on the web; treat it with care, and it will serve your purposes flawlessly.

If you enjoyed reading this article on how to do a WordPress database cleanup, you should check out this one about how to do a WordPress database reset.

We also wrote about a few related subjects like WordPress database schema, how to find and replace URLs in the WordPress database, and how to scan the WordPress database for malware.


Bogdan Radusinovic
Bogdan Radusinovic

Senior SEO and Marketing Specialist

Articles: 137