How to delete orphan wordpress wp_postmeta rows

WordPress keeps the post meta values in wp_postmeta table. When a post is deleted, its meta data may become orphan. It is harmless data but it may be good idea to clean for better database performance. Here is how the table wp_postmeta looks like:

Follow these steps to delete orphan values from table wp_postmeta. Note that the table name may have a prefix different from “wp_” depending upon your wordpress installation options.

1. Take a backup of the table wp_postmeta using the following SQL.

   CREATE TABLE wp_postmeta_pre_clean_bak SELECT * FROM wp_postmeta;
   

   You may get a message like “2482 rows affected, taking 293 ms”. Ensure that backup table is created and has data.

2. Run the following query to find orphan rows in table wp_postmeta.

   SELECT *
   FROM wp_postmeta 
   LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
   WHERE wp_posts.ID IS NULL
   

   This will show us the rows which needs to be deleted. Note down the number of rows.

3. Run the following query to delete orphan rows from table wp_postmeta.

   DELETE wp_postmeta
   FROM wp_postmeta 
   LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
   WHERE wp_posts.ID IS NULL
   

   Note the number of rows deleted from the output message. It should be same as number of rows we got in above step.