WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] SQL Query to delete orphans wp_postmeta (10 posts)

  1. tis01
    Member
    Posted 4 years ago #

    After a few upgrades and transfer, my database have now : 25 000 posts (and each has only 1 custom field). But for some reasons wp_postmeta has 720 000 rows with different post_id, wp_term_relationships has 120 000 records.

    I used this topic http://wordpress.org/support/topic/311665 to delete useless tag and now I really need to make same thing for wp_postmeta and wp_term_relationships .

    Thanks

  2. MichaelH
    Member
    Posted 4 years ago #

    25 000 posts (and each has only 1 custom field)

    Does every post have 1 custom field that has the same key?

    Postmeta has a lot of records for locking posts and other stuff, so if your posts have one custom field with the same key, say the key is 'my_custom_key' then this SQL in phpMyAdmin could work:

    DELETE FROM wp_postmeta WHERE wp_postmeta.meta_key != 'my_custom_key'

    Remember Page Templates attached to Pages have records in this table. You might want to look for other exceptions before proceeding.

    Or course, backup your database before attempting anything like this.

  3. vtxyzzy
    Member
    Posted 4 years ago #

    This plugin might do what you want: http://wordpress.org/extend/plugins/optimize-db/ .

    You can do a plugin search with the kewords 'optimize database' to find more plugins like this one.

  4. MichaelH
    Member
    Posted 4 years ago #

    vtxyzzy - that plugin looks like it optimizes tables, but don't see any DELETE statements.

  5. tis01
    Member
    Posted 4 years ago #

    Thanks for response :

    Here is the content of wp-postmeta (query : SELECT meta_key, COUNT( meta_key ) FROM wp_postmeta GROUP BY meta_key)

    embed 1
    thumbnail 715625
    title 2
    _edit_last 95
    _edit_lock 95
    _wp_attached_file 1
    _wp_attachment_metadata 1
    _wp_old_slug 3

    So I have 715 625 custom field thumbnail but only 25 000 posts with 1 custom field thumbnail = 690 000 more. So I would like to identify thumbnail which don't match with existing post-id (from wp-posts) and delete theses from the wp-postmeta table.
    Of course I need to keep the 25 000 good custom field data from wp-postmeta.

  6. MichaelH
    Member
    Posted 4 years ago #

    Well this will get all records in postmeta that don't have corresponding records in the posts table.

    SELECT *
    FROM wp_postmeta pm
    LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
    WHERE wp.ID IS NULL
  7. tis01
    Member
    Posted 4 years ago #

    Works great. Thanks for your help

  8. rhythmdoctor
    Member
    Posted 4 years ago #

    @MichaelH

    Thanks as I see that query helps identify the redundate postmeta. However, what would be the mySQL command to delete those entries? Thanks

  9. Mark / t31os
    Moderator
    Posted 4 years ago #

    Delete for Michaels query (untested).

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

    An alternative select..

    SELECT * FROM wp_postmeta
    WHERE NOT EXISTS (
    	SELECT * FROM wp_posts
    	WHERE wp_postmeta.post_id = wp_posts.ID
    );

    As delete.

    DELETE wp_postmeta FROM wp_postmeta
    WHERE NOT EXISTS (
    	SELECT * FROM wp_posts
    	WHERE wp_postmeta.post_id = wp_posts.ID
    )

    Any of these queries should be preceded by a backup of important data, we will not take responsibility if something goes wrong, so please be sure your important data is backed up first.

  10. adambundy
    Member
    Posted 3 years ago #

    I had the same issue (orphan metadata from deleted posts) and ran Mark's version first:

    DELETE wp_postmeta FROM wp_postmeta
    WHERE NOT EXISTS (
    	SELECT * FROM wp_posts
    	WHERE wp_postmeta.post_id = wp_posts.ID
    )

    which deleted ALL of my postsmeta table! After restoring that table, I ran Michael's version:

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

    which worked like a charm. Thanks Michael!

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags