How to clean up the WordPress database: the definitive guide

Optimizing your database is important. You may not notice it if it’s a small site, but if you have been running it for a while and/or have a large site, you’ll probably see and feel a difference when you clean up your database.

Every environment and setup is different, so there is no “magic code” that will do this for you. But we will give you all sorts of optimization tips and tricks for the most popular database management system for WordPress, MySQL and MariaDB.

Stop here. The database is the most important component of your website. It is very delicate. You must back up your entire website, including the database, before making any changes to the database itself. If you don’t, you risk losing website data. We are also not responsible for any damage you cause to your website.

In this article:

  1. Delete orphaned and duplicated metadata
  2. Clean tables of uninstalled plugins
  3. Clean up WordPress default database tables
  4. Table optimization

1) Delete orphaned and duplicated metadata

WordPress has a variety of entries that contain additional information on your database. For example, your users have an email address, a website, a bio; your posts have a content, a title, an excerpt; comments have an author, an email field, and so on. This information can sometimes be duplicated or orphaned (not belonging to anything). In such cases, this data is simply worthless and can make the WordPress database cluttered. Therefore, they should be cleaned up to improve performance and get a neat database.

First of all, let us define the terms “orphaned” and “duplicated”:

  • Orphaned metadata: In WordPress, this refers to data that is no longer associated with a particular post or page, user, comment, term, or other item. This can occur if the item in question is improperly deleted or if the data is imported or exported incorrectly.
  • Duplicated metadata: This refers to data that is duplicated in the WordPress database, which can lead to confusion and inconsistency in the information. This can happen, for example, when a post or page is imported or exported incorrectly, or when data is entered manually multiple times. Sometimes a bad plugin that is not coded properly can also be the cause.

Now let us look at the individual types of metadata.

  • Post metadata: The post metadata is the information you provide viewers about each post, such as the publication date or author or unused post tags or unused custom post fields. Post metadata can also be internal technical information, such as the post template used. The database table used for post metadata is postmeta.
  • User metadata: User metadata is data associated with a user account, such as name, email address, and profile information. Orphaned user metadata is user metadata that is no longer associated with a user account. The database table used for user metadata is usermeta.
  • Comment metadata: Some examples of comment metadata in WordPress are the commenter’s name, email address, IP address, and website URL. Other metadata may include the content of the comment, the date and time the comment was made, and the status of the comment (approved, pending, spam, etc.). The database table used for the comment metadata is commentmeta.
  • Term metadata: The term metadata allows you to store data for terms (tags, categories, or other custom taxonomies) in a similar way to post metadata. This metadata can include information such as term descriptions, term images, and custom term fields. The termmeta database table (used by the term metadata) can also be used by WordPress to track and analyze user behavior and activity related to terms.
  • Term relationship: Posts or pages are associated with categories and tags, and this association is maintained in the term_relationships database table. This is called the term relationship.

Delete orphaned and duplicated metadata with a plugin

To clean up all this data, you can use a plugin like WP-Sweep. Just install it, go to Tools > Sweep and start “sweeping” all the data you need!

Delete orphaned and duplicated metadata manually

However, you can also do it the sadistic way manually. If you have access to a database management tool like phpMyAdmin, you can run the following queries to select the metadata you want and then delete it using your favorite method by either running the appropriate DELETE query or selecting the rows to delete with the mouse.

Remember to replace the database prefix from all the database names with your actual database prefix, which could be different than wp_.

Select all orphaned post metadata

SELECT * FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

Select all orphaned user metadata

SELECT * FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users);

Select all orphaned comment metadata

SELECT * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

Select all orphaned term metadata

SELECT * FROM wp_termmeta WHERE term_id NOT IN (SELECT term_id FROM wp_terms);

Select all orphaned term relationship

SELECT tr.*, tt.*, wt.name, wt.slug FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN wp_terms AS wt ON tt.term_id = wt.term_id WHERE tt.taxonomy != 'link_category' AND tr.object_id NOT IN (SELECT ID FROM wp_posts);

Select all duplicated post metadata

The result will show the number of duplications (column count) of any meta_key and meta_value combinations for any post_id.

SELECT COUNT(meta_id) AS count, post_id, meta_key, meta_value FROM wp_postmeta GROUP BY post_id, meta_key, meta_value HAVING count > 1;

Select all duplicated user metadata

The result will show the number of duplications (column count) of any meta_key and meta_value of any user_id.

SELECT COUNT(umeta_id) AS count, user_id, meta_key, meta_value FROM wp_usermeta GROUP BY user_id, meta_key, meta_value HAVING count > 1;

Select all duplicated comment metadata

The result will show the number of duplications (column count) of any meta_key and meta_value of any comment_id.

SELECT COUNT(meta_id) AS count, comment_id, meta_key, meta_value FROM wp_commentmeta GROUP BY comment_id, meta_key, meta_value HAVING count > 1;

Select all duplicated term metadata

The result will show the number of duplications (column count) of any meta_key and meta_value of any term_id.

SELECT COUNT(meta_id) AS count, term_id, meta_key, meta_value FROM wp_termmeta GROUP BY term_id, meta_key, meta_value HAVING count > 1;

2) Clean tables of uninstalled plugins

Sometimes a plugin leaves its database tables in the database even if you remove it. These tables contain information that is used by the actual plugin. So if the plugin is not going to be used in the near future, this information should be removed.

For this purpose we use the Plugins Garbage Collector plugin.

1) Install the plugin.

2) Go to Tools > Database Cleanup.

3) Make sure that Search none-WP tables is selected and click Scan Database.

4) Wait for the scan to complete and the plugin will show you a list of all database tables found, highlighted in different colors.

  • Green means that the table belongs to a plugin that is active.
  • Blue means that the table belongs to a plugin that is installed but deactivated.
  • Red means that the table belongs to a plugin that has been uninstalled.

Attention! The plugin must be used only to help you make your decision. You must not blindly rely on its results, because it is not perfect. For this reason, we recommend you to proceed as follows.

5) Take each of the red tables, remove the prefix and search them on Google to find out which plugin they belong to. For example, let us try to find out which plugin the wp_wpr_rucss_resources table belongs to:

  1. We remove the database prefix that is present in all tables. In our case, this is wp_. So in the end we have wpr_rucss_resources.
  2. Google “wpr_rucss_resources”, including quotes: https://www.google.com/search?q=%22wpr_rucss_resources%22&oq=%22wpr_rucss_resources%22&sourceid=chrome&ie=UTF-8
  3. We find out that the table belongs to WP Rocket.
  4. Time to decide. Did we actually uninstall the plugin and will we not need it in the future? Then we check the box right next to the table name, not the other one.

Some useful advice for working with this plugin.

  • False positives are more common than false negatives. In other words, the plugin will more often find tables that belong to a plugin but are marked as “Unknown” than tables that do not belong to any installed plugin but are marked as belonging to a plugin.
  • Don’t delete anything that you don’t know what it belongs to.
  • If you have red tables together with blue/green tables that have the same naming pattern (e.g. wp_wpf_modules, wp_wpf_meta_values_bk, wp_wpf_meta_values…), don’t delete them. It may be that the plugin simply did not recognize the red tables correctly.

3) Clean up WordPress default database tables

A default installation of WordPress has the following database tables:

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_termmeta
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

Like any other WordPress database table, these can be filled with unnecessary information that you can clean up.

wp_options

This is by far the most important table in WordPress, which is why it’s crucial to optimize it. If you do not know what you are doing, your site may stop working, because this table contains information not only about the options or settings of your plugins, but also about WordPress itself, such as the site URL or the permalink structure.

There are a few optimization tasks to do here.

Delete transients

In WordPress, transients are a way for plugins to temporarily cache data in the database, such as the results of complex database queries. Transients help you improve the performance of your website because they reduce the number of times that your website needs to run the same query.

When a plugin creates a transient, it optionally sets an expiration date for it. This is both a logical (you do not want to retrieve old data) and maintenance step (it prevents clutter in the database). WordPress deletes the transient when it is requested by the plugin and WordPress sees that the expiration date has been reached.

Now, there is a problem. What if a plugin sets a lot of rows with transients, but you delete it and the plugin does not clean up after itself on deletion? You then have a lot of transients that will not be requested again and therefore cannot expire. So these transients will stay in the database for years and just bloat it.

You could manually check which transients belong to which plugin, but since this is a very tedious task, we recommend the following:

  1. Delete all transients with WP-Sweep.
  2. Wait for about a month.
  3. Check again how many transients you got with WP-Sweep. If you have about the same number, it means that the deleted transients were actually used and recreated. If you have a much lower number, congratulations! You have deleted transients that you did not need.

Since transients are just cached data, it should not be a problem to delete them. However, you should do this when your site has little or no activity, especially if you have current e-commerce or logged-in user sessions.

Clean up autoloaded data

Autoloaded data is data that is loaded on every page of your WordPress website and is set with the autoload field in a database table.

As mentioned earlier, the wp_options database table contains all sorts of important data for your WordPress site, such as the site URL, plugin settings, theme settings, etc., but it also often contains data that is no longer used because it was left behind by themes/plugins that have already been deleted. Considering that one of the main problems of WordPress websites is a large amount of autoloaded data in the wp_options table – many developers set the autoload field for their plugins/themes to yes by default, even though not every plugin should theoretically load its data on every page – we should prioritize eliminating unnecessary autoloaded data to avoid slower loading time.

Removing autoloaded data

So our goal should be to locate all autoloaded options that are no longer needed. The best plugin for this, if you do not want to get your hands dirty with phpMyAdmin, is Advanced DB Cleaner. On the Options tab, you can view the entire wp_options table, filter for autoloaded data and remove it from there. Let us take a look at how to do that.

1) Go to Tools > WP DB Cleaner > Options.

2) Sort by Size.

3) Now go from top to bottom and look only for the options where Autoload is set to yes.

4) For each autoloaded option, look it up on Google and find out what it belongs to. It can be a plugin, a theme or WordPress itself. Remove it only if you are sure you don’t need it.

The total size of autoloaded data should not exceed 1 MB, and we should aim to have less than 500 KB. The question is how we can check this total size. For this we have this plugin: Autoload Checker.

Verifying total size of autoloaded options

1) Install the Autoload Checker plugin.

2) Go to Tools > Autoload Checker.

3) You will see the total size on the top of the screen.

Delete unused options

But not only the autoloaded options need to be cleaned up. As with any other database, you should get rid of everything that is not needed. Many plugins leave their settings in the wp_options table after uninstallation and do not even provide a setting to remove them, which is a bad practice.

You can use the Advanced Database Cleaner plugin for this, but the feature that allows you to determine which plugin a certain option belongs to is only available in its paid package. That is, if you do not want to spend money, you’ll have to rely on our favorite friend Google. You know the drill:

  1. Go to Tools > WP DB Cleaner > Options.
  2. For each option, look it up on Google and find out what it belongs to. It can be a plugin, a theme or WordPress itself. Remove it only if you are sure that you do not need it.

wp_posts

The wp_posts table contains every single post, page or custom post type of your website. contains every single post, page or custom post type on your site. So the most obvious way to reduce its size is to delete all unnecessary posts, pages or custom post types.

However, within this table are also the revisions. Revisions are old versions of your posts or pages that are stored in the database in case you want to restore them. Sometimes the number of these versions can become too large, taking up space in the database and, in the worst case, slowing down your website.

Again, you can use WP-Sweep, but keep in mind that it does not allow you to keep a certain number of revisions. It removes all or nothing. If you want to keep a specific number of revisions, you can use the Advanced Database Cleaner plugin and set it to keep the number of days of revisions you choose.

wp_postmeta

This particular database table is quite problematic. There is no obvious way to clean it up, so everything has to be done by hand. Not only that, it’s a table that tends to grow quickly, especially if WooCommerce has been in use for years (the problem has now been solved).

Unfortunately, to clean up the postmeta table, we need to go through all the existing metadata and determine if it is currently in use. To do this, we use two SQL queries that we can run from phpMyAdmin or other database management software. The first query is as follows:

SELECT MAX(t1.post_id) AS `post_id`, MAX(t2.post_date) AS `Date`, SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`, (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_postmeta AS t1
    JOIN wp_posts AS t2 
        ON t1.post_id = t2.ID
    WHERE meta_key NOT LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;

The second query is exactly the same, except that the WHERE clause is changed so that the lines with a leading underscore in the meta_key field are excluded. This is done because the meta_key values are distinguished by a leading underscore (for secret/default settings) or the absence of an underscore (for user-accessible settings in the admin dashboard), which requires a search for both cases.

SELECT MAX(t1.post_id) AS `post_id`, MAX(t2.post_date) AS `Date`, SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`, (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_postmeta AS t1
    JOIN wp_posts AS t2 
        ON t1.post_id = t2.ID
    WHERE meta_key LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;

Let’s take a look at an example of what we get as a result of the first query and how we proceed afterwards. You should proceed in the same way with the second query, remember!

  • post_id shows us the most recent post ID to which the metadata in question belongs.
  • Date shows us the date of the above post_ID above. Based on the date and the post ID, we can determine whether the metadata in question can be deleted or not. In the previous case, for example, we are not allowed to delete the metadata with the meta_key containing wp_featherlight because the last metadata was created recently (we are writing this in 2024) and the post ID to which it belongs is one of the most recent in the posts table. And, well, we also have the WP Featherlight plugin installed…
  • Meta is the prefix for each group of metadata. The query is built to display only the first two strings, taking underscores as separators. This makes it easier for us to separate the groups by plugin, theme or component. For example, wp_featherlight is the prefix for all metadata named wp_featherlight_disable, but it could also contain wp_featherlight_enable or anything else:
  • Size is the data size of each group of prefixes, in megabytes.
  • Count is the number of rows of each group of prefixes.

In our example, we determined that we are not using the publicize_twitter entries because the last post ID that used them was 20460, while we are already at 37015. In addition, this post (20460) was created back in 2019, about 5 years before the time we are writing the article. Also, all publicize_twitter entries belong to Jetpack, which was already uninstalled on the site.

Therefore, we can run the DELETE query to get rid of them. Make sure you run a SELECT query first, so that you don’t delete anything important.

SELECT * FROM wp_postmeta
    WHERE meta_key LIKE 'publicize_twitter%';
DELETE FROM wp_postmeta 
    WHERE meta_key LIKE 'publicize_twitter%';

We would like to thank David Greenwald (rawkblog.com) for creating this amazing post where we took the information from.

oEmbed cache

WordPress stores all oEmbed data in the database, which is basically the markup from external resources like YouTube, Twitter or Google Maps, to improve performance when loading from the database. If there are a lot of old, deleted posts using oEmbed, they could clog up the database. In this case, this cache should be deleted.

Of course, you cannot know if there are many old deleted posts that have used oEmbed. Therefore you can safely clean this cache from time to time with, once again, WP-Sweep.

wp_comments

If you want to optimize the wp_comments table, it’s best to simply get rid of your spam and deleted comments. You don’t even need a plugin for this. Just go to your WordPress dashboard and delete them from the comments section.

wp_commentmeta

You’ll need to follow the same instructions as for the wp_postmeta table, but you’ll need to run the following queries. Feel free to refer to the wp_postmeta section.

SELECT MAX(t1.comment_ID) AS `ID`, MAX(t2.comment_date) AS `Date`, SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`, (SUM(LENGTH(meta_id)+LENGTH(t1.comment_ID)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_commentmeta AS t1
    JOIN wp_comments AS t2 
        ON t1.comment_ID = t2.comment_ID
    WHERE meta_key LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;
SELECT MAX(t1.comment_ID) AS `ID`, MAX(t2.comment_date) AS `Date`, SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`, (SUM(LENGTH(meta_id)+LENGTH(t1.comment_ID)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_commentmeta AS t1
    JOIN wp_comments AS t2 
        ON t1.comment_ID = t2.comment_ID
    WHERE meta_key NOT LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;

wp_users

Just as with the wp_comments table, the best way to reduce the size of the wp_users table is to delete users. The most obvious candidates are spam users and inactive users (although you should be careful with the latter, you should make it clear in your terms and conditions that you are allowed to do this). If your user count isn’t so big, you can simply delete them from the WordPress dashboard, but if you have a large number of users, you have two options.

  • Use WP Bulk Delete.
  • Use phpMyAdmin. In this case, you’ll need to follow these steps:
    • Run one or more SQL queries like DELETE FROM wp_users WHERE user_email = "%@example%";, replacing @example with any pattern you’ve recognized as spam. Inactive users don’t follow a pattern, so you can only delete them individually from the WordPress dashboard.
    • After running the query (or queries), you will need to delete the orphaned user metadata, as the queries in question will not delete them.

Unfortunately, it is difficult to identify spam users and inactive users. Spam is the easiest to identify because it usually follows one or more patterns, e .g. .ru e-mail addresses, random combinations of numbers and letters or things like NameSurname@UnknownDomain.com. Inactive users, on the other hand, are users who have no post or page assigned, no media, no comments, etc. It would be useful to know the date of the last login, but this is not built into WordPress by default.

wp_usermeta

You’ll need to follow the same instructions as for the wp_postmeta table, but you’ll need to run the following queries. Feel free to refer to the wp_postmeta section.

SELECT MAX(t1.user_id) AS `user_id`, MAX(t2.user_registered) AS `Date`,
SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`,
(SUM(LENGTH(umeta_id)+LENGTH(user_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_usermeta AS t1
    JOIN wp_users AS t2
        ON t1.user_id = t2.ID
    WHERE meta_key LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;
SELECT MAX(t1.user_id) AS `user_id`, MAX(t2.user_registered) AS `Date`,
SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`,
(SUM(LENGTH(umeta_id)+LENGTH(user_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_usermeta AS t1
    JOIN wp_users AS t2
        ON t1.user_id = t2.ID
    WHERE meta_key NOT LIKE '\_%'
    GROUP BY `Meta`
    ORDER BY `Date` DESC;

wp_terms

Just as with the wp_comments table, the best way to reduce the size of the wp_terms table is to delete terms, either categories or tags (or any additional term that you have created or that another plugin has created). The most obvious approach is to delete terms that are linked to 0 posts via the WordPress dashboard and that you know will almost never be used.

We do not recommend deleting tags via phpMyAdmin or a database management app as you will be dealing with orphan term metadata, relationships and taxonomies from the term_relationships, termmeta and term_taxonomy tables. However, this is the first query you will need if you want to see the terms with 0 entries:

SELECT * FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );

And then to delete them:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );

Again, if you have just executed the query, you need to make sure that there are no orphaned term metadata, relationships and taxonomies. See the Delete orphaned and duplicated metadata section.

wp_termmeta

You’ll need to follow the same instructions as for the wp_postmeta table, but you only need to execute the following query. Since the terms do not have a creation date, the results here are ordered by the size of the term metadata. The top results are the ones that take up the most space in the database. Feel free to refer to the wp_postmeta section.

SELECT MAX(t1.term_id) AS `term_id`,
SUBSTRING_INDEX(t1.meta_key, '_', 2) AS `Meta`,
(SUM(LENGTH(meta_id)+LENGTH(t1.term_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS `Size`, COUNT(*) AS `Count`
    FROM wp_termmeta AS t1
    JOIN wp_terms AS t2
        ON t1.term_id = t2.term_id
    GROUP BY `Meta`
    ORDER BY `Size` DESC;

wp_term_relationships

The only cleanup you can do in the term_relationships table is to remove all orphaned rows. This table stores the relationships between articles (or other content types) and the associated taxonomy terms. This table essentially serves as a junction table in a many-to-many relationship between posts and taxonomy terms, so it never contains non-orphaned junk; either the content is orphaned (and therefore junk) or it is not.

Please refer to the section Delete orphaned and duplicated metadata.

wp_term_taxonomy

The same applies as for term_relationships. This table contains information about the taxonomy terms used in a WordPress installation. Taxonomy in WordPress refers to the way content is categorized and includes categories, tags and any custom taxonomies defined by themes or plugins. Therefore, in this case you should also refer to the section Delete orphaned and duplicated metadata.

The wp_links table is a part of the WordPress database schema, but it’s not commonly used in newer WordPress installations. It was historically used to store data related to blogroll links, which were collections of links to other websites or blogs that bloggers wanted to showcase on their own sites.

4) Table optimization

Your database tables behave in some ways like the hard disk drives in Windows. Remember the word “defragmentation” that you used to see on Windows computers? Running a OPTIMIZE TABLE command on MySQL tables reorganizes the physical memory of the database table data and associated index data to save disk space and improve efficiency when accessing the table. You can optimize your WordPress database tables in 2 ways.

This can be time consuming depending on the size of the data and indexes, but as long as there is no power outage, it will not hurt the data. As we said, this process is similar to defragmenting a computer.

WP-Sweep

You can again use the WP-Sweep plugin and its Optimize Tables option.

phpMyAdmin

If you use phpMyAdmin, you can optimize your database tables with one click. To do so, follow these instructions:

1) Log in to your phpMyAdmin. If you do not know how to do that, your hosting provider can help you.

2) Click Databases in the upper area.

3) Select the appropriate database.

4) Click Check All (unless you want to select a specific group of tables) and then select Optimize table.

5) You should receive a confirmation message. You are done!

Newsletter Updates

Enter your email address below and subscribe to our newsletter

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *