De-duplicating MySQL rows in the WordPress post meta table

I recently needed to prune some duplicate rows from the WordPress post meta table. For reasons I won’t go into, there were two copies of many post meta values for most of the posts in the database and I wanted to remove the extra copies whilst keeping rest.

After trying various solutions (on a backup of the database) involving sub selects and unique keys, the simplest and fastest solution seems to be to create a new table and copy over rows I wanted to keep.

CREATE TABLE tmp_meta
LIKE wp_postmeta AS
SELECT MIN(meta_id) as meta_id, post_id, meta_key, meta_value
FROM wp_postmeta
GROUP BY post_id, meta_key, meta_value;

This creates a new table with the same specification as the wp_postmeta table and inserts only one copy of each row based on the post_id, key and value. Then it’s just a question of swapping the names of the tables and testing the result.

DROP TABLE wp_postmeta;
RENAME TABLE tmp_meta TO wp_postmeta;

Note that this will take the meta value with the lowest meta_id and drop the rest. As always, take a backup of the database before doing this on your actual site.