Website Optimizations

How to Remove Duplicate Records from WordPress `wp_postmeta` Table

June 29, 2023
9:56 pm

If you have had a WordPress website for a long time then chances are that it has become a bit bloated and sluggish through the years. We usually try optimizing website by cleaning up unused plugins and themes first since that makes the biggest difference in a short amount of time. But don’t overlook the database for performance optimization. And when you examine the database you will probably find that one of the tables with the most records is the wp_postmeta table. And with a little checking you may find that a significant number of those records are duplicates. This optimization will remove duplicate records from the wp_post_meta table.

We were recently looking at optimizing one of our website’s databases and identified the wp_postmeta table for cleanup. Before we optimized it there were around 154,332 records representing about 96.6 MB of disk space used. By optimizing the table we were able to delete 95,024 unnecessary records bringing the table size down to 59,308 records and 51.6 MB of disk space. That represents a 61% reduction in table rows and 53% reduction in disk space.

Today we will look at how to trim a bloated database by removing duplicate records from the wp_postmeta table. To do this we will:

Explain what the wp_postmeta is so that you know what it does.
Run a SELECT query to find duplicate records without removing them to learn just how many records are in the table. This will let us know what results to expect and help us decide if we actually want to modify our table or not.
Run a DELETE query that will remove the duplicate records permanently.

About the `wp_postmeta` Table

The wp_postmeta table is a supporting table for the wp_posts table. (There is a handy database diagram in the WordPress Codex of how the WordPress tables fit together.) Your website’s pages, posts, drafts, uploaded pictures, and many other pieces are stored in the wp_posts table. However the dynamic information about those items are stored in the wp_postmeta table. This includes post categories, draft statuses, image sizes, and more. Most wp_post records in the database have MANY wp_postmeta records describing them. This means that the wp_postmeta table can grow very large depending on how many records you have in the wp_posts table. The wp_postmeta table is made of 4 columns.

meta_id – The unique identifier for each record in the table.
post_id – The wp_post record that this metadata is for.
meta_key – Descriptive text used used to identify the metadata.
meta_value – The actual metadata stored.

The meta_id value will be unique for every row in the table. (Unless the table has been corrupted! And in which case you have other problems to worry about than cleaning the table.) Therefore we will have to use the other 3 columns to identify duplicate records.

Finding Duplicate `wp_postmeta` Records

First, before proceeding, make absolutely certain that you have backed up your database! All it takes is one little typo in a query for you to have a very bad day. I might additionally want to make a quick backup of the wp_postmeta table by itself for easy restoration.

To find duplicate records in the wp_postmeta table we will use the following MySQL query. We want to be extremely cautious when remove records so we will only count a record as a duplicate if its post_id, meta_key, and meta_value exactly match another record in the table. It is possible for the table to contain records with matching post_ids and meta_keys but differing meta_values. However there may be scenarios where you do not want to remove those records which is why we are not providing a code sample for that.

SELECT
	`b`.*
FROM
	`wp_postmeta` AS `a`,
	`wp_postmeta` AS `b`
WHERE
		`a`.`meta_id` &lt; `b`.`meta_id`
	AND (`a`.`post_id` = `b`.`post_id`)
	AND (`a`.`meta_key` = `b`.`meta_key`)
	AND (`a`.`meta_value` = `b`.`meta_value`)
GROUP BY
	`b`.`meta_id`

After running this query you will see how many duplicate records there are in your table. If your still wanting to remove them then proceed to the next step.

Removing the Duplicate `wp_postmeta` Records

In order to purge the wp_postmeta table use the following query. This will permanently delete the duplicate records from the database. You may notice that it is the same as the SELECT query above, but with only the first two lines changed.

DELETE
	`b`
FROM
	`wp_postmeta` AS `a`,
	`wp_postmeta` AS `b`
WHERE
		`a`.`meta_id` &lt; `b`.`meta_id`
	AND (`a`.`post_id` = `b`.`post_id`)
	AND (`a`.`meta_key` = `b`.`meta_key`)
	AND (`a`.`meta_value` = `b`.`meta_value`)

Conclusion

Cleaning the wp_postmeta table may seem like a daunting task, but with 2 small database queries it is possible to clean it up in record time!

Need more help? Check out our knowledge base for more help articles or contact us for help with your website.