How to Do a WordPress Database Cleanup

You need a WordPress database cleanup?

Ouchie!

Actually, it’s not that bad. Don’t feel sorry for being in this situation.

Doing regular cleanups is actually a healthy way of keeping your WordPress site out of trouble.

A WordPress website consists of two different parts: files and a database. The files include media, plugins, and themes.

The database contains information about settings, posts, metadata, users, etcetera. The WordPress database is the website’s filing cabinet.

It stores all content, like posts, pages, comments, revisions, and spam comments. Another important factor is that it contains the settings for themes and plugins.

After using the site for some time this database gets cluttered up with obsolete and irrelevant information.

The user can significantly reduce the database size by removing this unnecessary data. This will improve the backup speed, as well as other performance parameters.

There are various ways to perform a WordPress database cleanup. Here are several MySQL queries that help with cleaning up a WP-database in phpMyAdmin.

There are also several useful plugins that make this job even less cumbersome.

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 yoursite.com with the URL of your website.

UPDATE wp_posts SET post_content = replace(post_content, 'http://yoursite.com', 'https://yoursite.com');

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

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

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

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

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

What is a WordPress database cleanup?

Oh, a WordPress database cleanup is simply the process of removing unnecessary clutter from the database. It can help to improve the overall performance of your WordPress site. The cleanup may involve removing items like spam comments, revisions, or old drafts that you no longer need. The idea is to keep your database lean and mean for smooth operation.

Why should I bother cleaning up my WordPress database?

Well, you see, cleaning up your WordPress database can significantly impact your site’s performance. It helps reduce the size of the database, making it more efficient when executing queries. This in turn can lead to faster page loading times and better overall user experience. Plus, a smaller database is easier to maintain and backup, saving you time and effort.

How often should I perform a WordPress database cleanup?

That’s a good question. It’s generally recommended to perform a database cleanup every 3 to 6 months, but this can vary depending on your site’s activity level. If you have a high-traffic site with loads of content being added or removed frequently, you might wanna do it more often. Just keep an eye on your database size and performance to decide when it’s time for some cleaning.

Can I do a WordPress database cleanup manually?

Sure thing! You can absolutely perform a WordPress database cleanup manually using phpMyAdmin or another database management tool. You’ll need to access your hosting account’s control panel, locate your database, and start removing unnecessary data like revisions, drafts, or spam comments. But, be careful! Always back up your database before making any changes, just in case something goes wrong.

Are there any plugins to help me with WordPress database cleanup?

You bet! There are quite a few WordPress plugins out there that can help you clean up your database. Some popular ones include WP-Optimize, WP-Sweep, and Advanced Database Cleaner. These plugins can help you automate the cleanup process, making it easy to remove unnecessary data, optimize tables, and even schedule regular cleanups. Just pick one that suits your needs and get started!

What’s the difference between optimizing and cleaning a WordPress database?

Ah, good one. Cleaning a WordPress database involves removing unnecessary data like revisions, drafts, and spam comments. On the other hand, optimizing a database is more about improving the performance of the tables within the database. When you optimize a database, it reorganizes the data and indexes, reducing storage space and improving query efficiency. So, while they’re related, they serve slightly different purposes.

Are there any risks involved in cleaning up my WordPress database?

There can be some risks, yeah. If you’re not careful, you might accidentally delete important data or break your site. That’s why it’s crucial to backup your database before doing any cleanup. And if you’re not confident in your technical skills, it’s always a good idea to seek help from a professional or use a reputable plugin to handle the cleanup for you.

Can a WordPress database cleanup help with security?

Indeed, it can! A well-maintained and clean database is less vulnerable to security threats. By removing outdated or unnecessary data, you reduce the potential attack surface for hackers. Plus, a leaner database makes it easier to spot any suspicious activity, helping you stay on top of your site’s security.

What should I do before performing a WordPress database cleanup?

First and foremost, back up your database! This is super important, as it ensures you can recover your data in case something goes wrong during the cleanup. You might also want to inform your site’s users about any scheduled maintenance, just so they know what to expect. And finally, make sure you have a solid plan in place for what you want to clean up and optimize.

Can I undo a WordPress database cleanup if something goes wrong?

Well, that’s where having a backup comes in handy! If you’ve backed up your database before performing the cleanup, you can restore it to its previous state in case something goes wrong. That’s why it’s so important to have a backup before making any changes to your database. If you’ve used a plugin for the cleanup, some of them also have built-in undo features, but it’s still better to be safe with a backup.

 

Ending thoughts on doing a WordPress database cleanup

The most important advantage of the internet is its speed. So to maintain this speed it is good practice to optimize your website.

This is key to being a successful website owner.

A website’s source of fuel is its database, so don’t ignore it. If it expands uncontrolled the results are often unexpected and unpleasant.

A WordPress database cleanup is a necessary site maintenance procedure. It optimizes performance and gives the visitor an enjoyable experience.

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 url in WordPress database and how to scan WordPress database for malware.

 


Bogdan Radusinovic
Bogdan Radusinovic

Senior SEO and Marketing Specialist

Articles: 137