How to Do a WordPress Database Cleanup

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

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.

Ending thoughts on 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 about how to do a WordPress database cleanup, you should read this one on doing a WordPress database reset.

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

 

Up Next:

How to Do a WordPress Database Reset Properly

How to Do a WordPress Database Reset Properly