Logo Background

Why Clean Up WordPress WP_PostMeta Table?

  • By on September 12, 2011 | 2 Comments

    Looking for script to clean up wp_postmeta table in WordPress? This article share on how to reduce the wp_postmeta table size and improve the performance of your blog.

    As you know, wp_postmeta table hold data related to wp_posts. Example – All In One SEO Pack will create entries in wp_postmeta table if you define the keyword or description for each blog post.

    WordPress will do a join operation between wp_posts and wp_postmeta table each time when people visit your blog post based on the id key.

    So the rule of thumb is to keep wp_postmeta table size as small as possible to reduce the time consumed in table join operation.

    Delete WordPress Plugin Orphan Records In WP_PostMeta

    Okay. I know you assume that all the WordPress plugins will clear their respective entries from the database once you deactivated and deleted it.

    But most of time our assumption isn’t right. Let’s check out the meta_key records in wp_postmeta table by using the query as below. You will be surprise to find out there’s bunch of orphan records living inside there.

    SELECT meta_key, COUNT(meta_key) FROM wp_postmeta GROUP BY meta_key;

    I saw lots of records is related to the WP To Twitter plugin which was removed long time ago.

    I proceed to execute the clean up script as below after created backup for the WordPress database.

    DELETE FROM wp_postmeta WHERE meta_key = '_jd_post_meta_fixed';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_jd_tweet_this';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_jd_twitter';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_jd_wp_twitter';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_bitly';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_clig';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_target';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_url';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_wp';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_wp_jd_yourls';
     
    DELETE FROM wp_postmeta WHERE meta_key = 'jd_wp_twitter';

    There’s high number of count for both _edit_lock and _edit_last meta_key and I proceed to remove the records.

    No error detected when create, edit and publish blog post after the removal of _edit_lock and _edit_last meta_key. Voila!

    DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
     
    DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

    Additional tips: You may install Clean Options plugin to the orphan records in wp_options.

    Previous
    Next
    » Create Google Style Breadcrumb In Wordpress
  1. #1 Mobile Themes World
    October 25, 2011 10:32 pm

    Thanks for sharing this tutorial. Is there any method to find all orphaned records. I have deleted few plugins which i installed on my website like YARP etc. But i have feeling that those database is still within my website. So how to find it and clean unused and deleted plugin database.

    Post ReplyPost Reply
  1. #2 Jing Hong
    October 29, 2011 2:34 am

    Hi. The table wp_options will become bulky after several attempt of install and uninstall of plugins.

    You may try to install Clean Options WordPress plugin which will look for orphan records in wp_options table. Usually I tried to keep wp_options table smaller than 100K if possible.

    You will notice the speed improvement of your site after housekeeping of this table. I recommend you to read other post written by me previously of WordPress housekeeping and speed optimization.

    8 useful SQL to clean WordPress database
    How to speed up WordPress using wp-config.php

    Post ReplyPost Reply
Leave a Comment