WordPress.org

1. tis01
   Member
   Posted 2 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
   Volunteer
   Posted 2 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 2 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
   Volunteer
   Posted 2 years ago #

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

5. tis01
   Member
   Posted 2 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
   Volunteer
   Posted 2 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 2 years ago #

   Works great. Thanks for your help

8. rhythmdoctor
   Member
   Posted 1 year 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 1 year 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 1 year 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.