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:
wordpress-wp-postmeta-table-sample-data

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.

Share this article: share on Google+ share on facebook share on linkedin tweet this submit to reddit

Comments

Click here to write/view comments