Table of Contents

Are you experiencing any performance issues on WordPress? Cleaning up your database is a necessary step in your optimization journey. If you’ve been using WordPress for a while, chances are your database is cluttered and filled with tables you no longer need. It looks like it’s time for a clean-up! But where to begin? Which content can be deleted without compromising the smooth running of your website? That’s precisely the purpose of this guide: you’ll understand which content you can delete and how to optimize your WordPress database. 

WordPress database clean-up - Source: WP Rocket
WordPress database clean-up – Source: WP Rocket

Why Clean Your WordPress Database

Cleaning your database makes it run more efficiently and helps boost performance by clearing up significant space. If you delete all the stale and outdated data, you can remove the bloat accumulated over time (which could have harmed loading times). 

Your WordPress database is a mix of all your site’s essential content, including themes, plugins, comments, posts, pages, and revisions. While some elements should never be deleted, it will not harm to trim the excess of old information around three main optimization areas: 

  1. Reduce the database size from unnecessary entries 
  2. Index fewer data on tables subject to clean up
  3. Rebuild indexes

By following those three rules, you’ll optimize your database, improve the performance of your website and significantly reduce server loads.

💡Did you know? Cleaning up your WordPress database may also fix a slow WordPress admin

Let’s go over the 10 applicable tips you can use for a good clean-up of your WordPress database. 

10 Best Practices to Clean and Optimize a Database on WordPress

With WordPress, you can optimize your database in a few different ways. We have put together 10 best practices you can follow to clean up your WordPress tables – and it should always start with a backup!

1. Backup Your Database

Always take a database backup before starting the cleanup and deleting any tables. If you happen to remove the wrong data, the whole website could go down. You can use FTP, your hosting panel, or a WordPress plugin like UpdraftPlus to complete this task and be on the safe side.

2. Delete Old Plugins, Themes, and Post Data

If you decide not to use a plugin or a theme, we recommend you delete them and all their data. 

To delete unused plugins, go to Plugins → Installed Plugins. Then, click Delete under any plugins you’d like to remove. Follow the same path from Appearance → Themes if you want to delete all the themes. 

In the same spirit, get rid of the posts and pages that have been sitting too long in your admin, and that will never be published. 

3. Delete or Limit Post Revisions

WordPress allows you to control how many revisions you want to keep for an article, which can be very handy if you need to restore an older version. To limit post revisions manually, simply add this code to your WordPress site’s wp-config.php file

define( 'WP_POST_REVISIONS', yournumberhere )

4. Delete Spammy and Unapproved Comments

Everybody who owns a WordPress blog knows how annoying it can be to get spam comments. Don’t let them pile up; delete them immediately to avoid clutter. Similarly, if you don’t approve a comment, make sure it gets deleted from the admin. Some anti-spam plugins like Akismet may be your best ally. 

You can use phpMyAdmin to bulk delete all the spam comments by opening the WordPress_Comments table as shown below.

(👉Jump to our full phpMyAdmin tutorial for clearing-up database)

WordPress comments table - Source: onehosting
WordPress comments table – Source: onehosting

5. Delete Unused Tags

Tags help organize your website’s content by categories. It can help a visitor in the navigation and the overall user experience. However, if some tags become obsolete, delete them from the tags section accessible from the posts > tags section. 

6. Delete Old Shortcodes

Shortcodes are handy to add extra functionality to WordPress posts and pages without writing any code. But what happens if you delete the plugin or don’t want to use a shortcode? Your website gets unnecessarily bloated, and the visitors may see the hooks of the shortcode instead of the content:

[some-random-shortcode-seen-by-visitors]

One of the solutions would be to go manually through the list of shortcodes and delete all the unused ones. Another way would be to use a plugin like Shortcodes Finder that lets you find and optimize all your shortcodes. 

7. Delete Pingbacks and Trackbacks

When your site includes a URL linking to another site, this sends an automatic server notification called a “pingback”. Trackbacks and pingbacks are methods for alerting blogs that you have linked to them, and the messages can fill up the database. To fix this issue, you need to disable both by heading to Settings > Discussion in WordPress and deactivate the first two settings:

Deactivating trackbacks and pingbacks - Source: WP Rocket
Deactivating trackbacks and pingbacks – Source: WP Rocket

8. Delete Transients

Transients are a type of temporary data cache, so you can use them to store data, but only for a while. You can check how many transients are being stored in your database tables by using phpMyAdmin, then you can search for the following option name in the database:

_ transient _%

Then, delete all the transient tables manually from the dashboard. You can also manage and delete transients in WordPress by using the Transients Manager plugin

9. Clean Orphan Data 

Orphaned data can appear in various forms within your database, such as unused metadata, unused terms or tags, unattached media files, and more. These data fragments take up valuable space and can clutter your database. An example of orphan data is a piece of information that still sits on an organization’s network (or server) but was created and owned by a now-deactivated employee.

You can use a plugin like WP-Optimize or WP Sweeper to clean orphan data. If you prefer to do it manually using a MySQL request, we recommend you read this tutorial about how to find the orphan data and delete it

10. Optimize Tables

The ultimate point of the database optimization journey is cleaning up the tables. WordPress has 11 tables by default, but more will be added according to the theme and plugin you install. 

WordPress database tables by default - Source: WP Rocket
WordPress database tables by default – Source: WP Rocket

Optimizing a database requires great care, and you must be cautious. We are sharing two ways of doing a full database clean-up: manually with phpMyAdmin or automatically with a WordPress plugin. Let’s go over both methodologies. 

How to Optimize Your WordPress Database With phpMyAdmin

To manually optimize your WordPress database, you will need to login into PHPMyAdmin via your hosting provider and follow the steps below:

  1. Log in to cPanel and open the PHPMyAdmin tool.
  2. Select the tables that need to be optimized from the structure tab. You can also select them by checking the “Check all” box below the table lists. 
Selecting the tables that need optimization - Source: phpMyAdmin
Selecting the tables that need optimization – Source: phpMyAdmin

3. Once the tables are selected, open the dropdown menu located next to the Check all button:

The dropdown menu to select the different options – Source: phpMyAdmin

4. Select the Optimize table option:

Optimize table option – Source: PHPmyAdmin

5. Once the database has been cleaned up and optimized, you should get this success message:

Clean up database’s confirmation message - Source: phpMyAdmin
Clean up database’s confirmation message – Source: phpMyAdmin

If you need to delete a whole database, head to the Databases tab, select the tables to delete, and click on the Drop button:

Delete a table - Source: phpMyAdmin
Delete a table – Source: phpMyAdmin

How to Optimize Your WordPress Database With a WordPress Plugin

The easiest and safest way to clean your database is to use a WordPress plugin. It will do the heavy lifting for you, and you won’t need to touch any of the tables to start your optimization journey. The best plugins to optimize your WordPress database are: 

  1. WP Rocket
  2. Advanced Database Cleanser
  3. WP-Optimize
  4. WP-Sweep
  5. Perfmatters

Let’s briefly see how database optimization works for each of them!

1. WP Rocket

One of the best caching plugins for WordPress that will optimize your database in one click. It’s also the most powerful extension to boost your loading time, improve your PageSpeed performance score, and optimize your Core Web Vitals

From the WordPress dashboard, open the Database tab and select the advanced options of your choice. You can limit the number of revisions, trash the spammy comments and transients, and schedule an automatic daily, weekly, or monthly clean-up. 

The database optimization tab - Source: WP Rocket
The database optimization tab – Source: WP Rocket

2. Advanced Database Cleanser

One of the most complete plugins that focuses only on database optimization. It will clean up your database by deleting orphaned items such as “old revisions”, “spam comments” and more advanced options. From the first tab, you can do a general clean-up:

The database optimization tab - Source: Advanced Database Cleanser
The database optimization tab – Source: Advanced Database Cleanser

If you want to be more thorough in the optimization, open the second tables tab and manually select the table that needs cleaning. 

3. WP-Optimize

WP-Optimize cleans the database, compresses images, and also caches pages. It clears out unnecessary data, cleans up your tables, and retrieves space lost to data fragmentation. There are also some stats about the database and the potential savings you could get after the optimization. 

The database optimization tab - Source: WP-Optimize
The database optimization tab – Source: WP-Optimize

4. WP-Sweep

The plugin lets you clean up unused, orphaned, and duplicated data in your WordPress database. It cleans up revisions, auto drafts, unapproved comments, spam comments, and more.  It also optimizes your database tables quickly and efficiently.

Database optimization - Source: WP-Sweep
Database optimization – Source: WP-Sweep

5. Perfmatters

Perfmatters is a premium WordPress plugin that comes with a dedicated database tab. In terms of optimization, you can disable or limit the number of post revisions that WordPress stores or automatically delete the spam comments to keep your database always clean. 

Database cleaning interface - Source: Perfmatters
Database cleaning interface – Source: Perfmatters

Wrapping Up

As a final note, there is one golden rule to follow; you should always keep only relevant information stored in the database and lower resource usage. You can achieve better performance and maintain good database health by regularly deleting outdated data.

If you have a technical background, optimizing your database using phpMyAdmin should be straightforward. But if you are looking for an automated tool that does the job for you, you can try WP Rocket. It has a dedicated section to completely optimize your database (e.g., the posts, the comments, the transients.). WP Rocket goes beyond database clean-up and offers caching, advanced JS and CSS optimization, lazy loading, and much more. If you want to speed up your WordPress site, and clear your code and database in a few clicks, WP Rocket is the right choice. Plus, you don’t take any risks with the 30-day money-back guarantee. 


Related Articles of Page speed and caching
Subscribe to Our Newsletter

Stay in the loop with the latest WordPress and web performance updates.
Straight to your inbox every two weeks.

Get a Faster Website
in a Few Clicks

Setup Takes 3 Minutes Flat
Get WP Rocket Now