How to Clean Up Your WordPress Database

Do you want a fast WordPress website? If so, you need to clean up WordPress database by removing unnecessary data.

WordPress database cleanup is an important maintenance task that will reduce page loading times on your website. It should be performed alongside other performance techniques such as caching pages, optimizing images, deferring Javascript, and removing unused CSS styling.

The WordPress database stores website content in posts, pages, and other post types. It also stores comments, links, portfolio items, form entries, plugin settings, theme settings, media settings, and more.

Unfortunately, if left unchecked, WordPress databases quickly become bloated with data that is no longer required. This increases the size of the database and the time it takes to retrieve information, which inevitably makes your website slower.

The goal of WordPress database optimization is to remove unnecessary data so that your website will be more efficient and load pages faster.

Let’s take a closer look at why WordPress databases increase in size and then review how you can clean up database in WordPress 🙂

Why Do WordPress Databases Increase in Size?

When you install WordPress, the WordPress database is created with 11 core tables. These tables will store information such as your website settings, posts, pages, comments, links, and users.

Whenever you update your website and add new content, the WordPress database will grow in size. For example:

• Add a New Page – A new row will be added to the wp_posts table to store the page content
• Receive a New Comment – A new row will be added to the wp_comments table to store the comment
• Upload an Image – A new row will be added to the wp_postmeta table defining the image URL

Whilst the core files of WordPress plugins and themes are stored in the WP-Content directory, all plugin and theme settings and content are stored in the WordPress database in new database tables and additional database rows.

So every time you activate new plugins and themes on a WordPress website, the database grows larger. Unfortunately, the vast majority of plugins and themes do not remove the tables and rows they added to the database when they are deactivated and deleted.

In addition to leftover plugin and theme data, WordPress databases can grow in size quickly due to spam comments, excess post revisions, unused media files, and expired transient options.

The Importance of Backing Up Your WordPress Site

WordPress database cleanup and optimization should not break your website, however, I strongly recommend performing a website backup before you make any changes. This allows you to revert to your backup database if you accidentally remove data that you intended to retain.

On my personal blog, I use BlogVault to automatically back up my website daily and use the BlogVault backup tool to perform one-off backups when required.

Good WordPress Database Cleanup Practices

By following good WordPress database cleanup practices, your website database should never grow too big.

Recommendation #1: Prevent Comment Spam

Spam comments and other unwanted comments can greatly increase the size of the WordPress database. You can reduce the effect of comments by disabling trackbacks and pingbacks and using anti-spam WordPress plugins such as Akismet, Antispam Bee and CleanTalk.

If spam becomes a major problem on your website, you may want to follow my lead and completely deactivate comments altogether.

Recommendation #2: Limit Post Revisions

The WordPress revision system saves a revision each time you save a draft or publish a post, page or custom post type. It also saves a copy of your content automatically every 60 seconds. These are known as auto-saves or auto-drafts.

I’ve always found the revision system useful as it allows me to compare older drafts of articles, but it can also be a great waste of resources. Whilst only one auto-save is ever stored for a post or page, WordPress does not have any limit to the number of revisions that are saved. This can result in hundreds of variations of an article being stored in the WordPress table.

A practical way to tackle this is to limit the number of post revisions that are saved. You can restrict WordPress to three post revisions by adding the following line to the wp-config.php file.

define( 'WP_POST_REVISIONS', 3 );

The WordPress revision system can be disabled with the following line.

define( 'WP_POST_REVISIONS', 0 );

In the quest for efficiency, some website owners do use the above code to disable post revisions altogether. This is something I personally advise against as it leaves you with no backups of draft articles. A better option is to limit the number of post revisions that are saved and then delete those revisions once the content has been published.

Recommendation #3: Remove Unnecessary WordPress Plugins & Themes

Don’t fall into the habit of leaving unactivated WordPress plugins and themes on your website. If they are not being used, their files should be deleted and their data should be removed from the WordPress database.

Some plugins and themes do have an uninstall option in the settings area which removes all data, but unfortunately, most WordPress products leave all their data behind. Whilst leftover data can be removed from the WordPress database manually, database optimization solutions such as WP-Optimize and Plugins Garbage Collector are safer and they simplify the process by highlighting all unused tables for you (more on this later).

Recommendation #4: Remove Unused Media

Media that is not being used on your website will still be stored in the WordPress database. This has become a problem that is difficult to avoid as WordPress plugins and themes frequently import media when they are activated, but they fail to remove them later. This can result in many unattached icons, screenshots, unused thumbnails and other unwanted images.

The WordPress Media Library shows which media is not attached to content, but it can be time-consuming to validate media and check each item to see if they are required. I recommend using the WordPress plugin Media Cleaner instead as it will search for media that is not being used and let you delete them all with one click. In just a few minutes, the plugin found over 1,200 unused images from my WordPress media library.

Recommendation #5: Optimise Each Database Table

If you’re comfortable using a database administration tool such as phpMyAdmin to edit the WordPress database, you can utilise the Optimize Table feature to reduce storage space and improve I/O efficiency. Experienced users can also do this using the MySQL statement OPTIMIZE TABLE.

Don’t worry if this is all too technical for you as popular WordPress database cleanup plugins let you perform this WordPress database optimization at the click of a button.

Clean Up WordPress Database Using Plugins

For most website owners, WordPress database cleanup plugins are the most practical way to clean the WordPress database.

1. WP-Optimize

WP-Optimize is an all-in-one WordPress optimization solution that offers database cleaning, page caching, image compression and CSS, HTML, and Javascript minification.

Its WordPress database optimization plugin allows you to optimize database tables and clean up post revisions, auto-saved drafts, trashed posts, and metadata. Spam comments, pingbacks, trackbacks and expired transient options, can also be removed. Optimizations can be performed manually or scheduled to be processed daily, weekly fortnightly or monthly.

One of WP-Optimize’s most useful tools is its database table scanner. It highlights tables that are not being used and shows the number of records and data size for each one. A link to the associated WordPress plugin is provided for each table and tables that are not required can be removed at the click of a button.

2. WP Rocket

WP Rocket is a complete WordPress performance solution that offers page caching, lazy loading for images, CSS and Javascript optimization, and database optimization.

From the WordPress database cleanup page, you can delete post revisions, auto-drafts, trashed posts, spam comments, trashed comments, and transients. WordPress database tables can also be optimized.

WP Rocket’s scheduling tool helps you automate WordPress database optimization. Automatic cleanups can be performed daily, weekly, or monthly.

3. Plugins Garbage Collector

The plugin that I frequently use to search the WordPress database for leftover tables is Plugins Garbage Collector. It works in the same way as WP-Optimize’s table scanning tool, highlighting tables that are not being used and showing the number of records and data size. Plugins Garbage Collector can also check your WordPress table structure and scan for hidden database tables.

Whilst currently installed plugins are correctly highlighted as active by Plugins Garbage Collector, the plugin does struggle to find associated WordPress plugins for unused database tables.

4. WP Sweep

WP Sweep is a database optimization solution that can clean up post revisions, auto drafts, spam comments, orphaned data, duplicated meta information, and more. There is an option to optimize database tables too.

On my personal blog, I limit post revisions to three, yet WP Sweep showed that I still had a whopping 2,179 post revisions. This represented 11.15% of the total size of my database.

In comparison to other WordPress database optimization plugins, WP Sweep highlights more orphaned data and duplicated meta information, but as there is no optimization scheduling functionality, you need to perform all database cleanups manually. This is not necessarily a bad thing as it forces you to review each database cleanup.

5. Perfmatters

Perfmatters is a WordPress security and performance toolbox that provides dozens of different tools to reduce page loading times. On its main options page, Perfmatters allows you to limit post revisions or disable them altogether. The auto-save interval can also be changed from the default value of minute to two, three, four, or five minutes.

On the main database optimization page, Perfmatters allows you to clean up post revisions, auto-drafts, trashed posts, spam comments, trashed comments, and transients. The database can also be optimized and you can schedule optimizations to be processed daily, weekly, or monthly.

Final Thoughts

WordPress database optimization is one of the most important maintenance tasks for a WordPress website. By removing unnecessary data from your website database, you will reduce the size of the database and reduce page loading times.

I believe WP-Optimize is the best overall WordPress database cleanup and optimization solution available to WordPress users today as it can remove unused data and scan for unused tables from deleted plugins too. Those of you who are already using WordPress performance solutions such as WP Rocket or Perfmatters may want to consider the WordPress database optimization plugins that are already available to them.

Good luck.

Kevin