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

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

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

Conclusion

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.