Logo Background

How To Clean WordPress Database

  • By on May 3, 2010 | 6 Comments

    Looking for ways to speed up by clean your WordPress database after you have more than thousands post and comment live on it? $HOME/tmp/mysql_slow_queries has the log entries that show all the long running SQL on your WordPress database where you need to do tuning.

    It is good practice to perform database backup of your site or blog before you attempt any table cleaning script.

    The latest WordPress version 2.9.2 has the capability of post revision saving in the database. This function will create additional record in wp_posts with ‘revision’ attribute each time you click on Save Draft button.

    From time to time, your wp_posts table will grow with lots of revision post living inside and thus it will slow down the MySQL table scan.

    You can disable the post revision function by adding in the line as below to wp-config.php

    define('WP_POST_REVISIONS', false);

    Bear in mind that you will not have the rollback capability to previous post after you disable it.

    You might think it is not a big deal as everyone can download WP-Optimize to purge or clean the post revision in WordPress database.

    We have the same idea at first but we were wrong as we found there’s orphan record living in the wp_term_relationships table.

    WP-Optimize cleaning plugins just purge the entry in wp_posts table with ‘revision’ attribute and thus you need to delete the entry in wp_term_relationships manually.

    Remember to backup your WordPress database before execute any DELETE statement below.

    WordPress Clean wp_term_relationships SQL

    DELETE FROM wp_term_relationships
    WHERE NOT EXISTS (
            SELECT * FROM wp_posts
            WHERE wp_term_relationships.object_id = wp_posts.ID
    );

    This script above will delete the orphan record without any entry in wp_posts in wp_term_relationships.

    You can choose to clean the wp_postmeta using the script as below. We perform the deletion as some of the deleted plugins has the orphan record entry left in wp_postmeta table.

    WordPress Clean wp_postmeta SQL

    DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
     
    DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";
     
    DELETE FROM wp_postmeta WHERE meta_key = "_wp_old_slug";
     
    DELETE FROM wp_postmeta WHERE meta_key = "jd_tweet_this";
     
    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 = "nofollow4post";
     
    DELETE FROM wp_postmeta WHERE meta_key = "ratings_score";
     
    DELETE FROM wp_postmeta WHERE meta_key = "ratings_users";
     
    DELETE FROM wp_postmeta WHERE meta_key = "ratings_average";
     
    DELETE FROM wp_postmeta WHERE meta_key = "wp_noextrenallinks_mask_links";
     
    DELETE FROM wp_postmeta WHERE meta_key = "_wp_page_template";
     
    DELETE FROM wp_postmeta WHERE meta_key = "_sexybookmarks_permaHash";
     
    DELETE FROM wp_postmeta WHERE meta_key = "_sexybookmarks_shortUrl";

    wp_options is the last giant table you want to perform the deletion on as it stores all the configuration setting of plugins installed in your WordPress.

    We recommend you to use Clean Options plugin to perform the cleaning instead where it will look for orphan record in wp_options. It is recommended to delete any unused plugin and backup your WordPress database before you do so.

    Previous
    Next
    » Putty SFTP Command Authentication Without Password
  1. #1 WP
    November 27, 2010 10:02 pm

    As checked today I found there’s long running SQL entry on mysql_slow_queries for wp_options table.

    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’

    Looks like there’s no other choice but to delete the WordPress not-in-use plugin to clean up the wp_options table.

    Post ReplyPost Reply
  1. #2 pizm
    November 29, 2010 11:11 pm

    My blog slowdown on the wp_options table as well. After installed the Clean Options plugin I have reduce the number of records in wp_options table.

    Although the slow SQL “SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’” is still around, the number of execution time has reduce to fetch lesser rows. Thanks for sharing.

    Post ReplyPost Reply
  1. #3 Hua Kang
    March 23, 2011 4:15 am

    I think I just made my WordPress run faster on a shared host, following your advices. Thank you!

    Post ReplyPost Reply
  1. #4 Jing Hong
    May 25, 2011 8:18 pm

    For those who has enable WordPress News, Incoming Links, WordPress Development Blog to show in the Dashboard. You might discover that the size of wp_options is getting bulky. Try to houskeep the wp_options table using the command as below. Remember to backup your WordPress application and database before doing so.

    delete from `wp_options` where `option_name` like ‘_transient_timeout_rss%’;

    delete from `wp_options` where `option_name` like ‘_transient_rss_%’;

    delete from `wp_options` where `option_name` like ‘_transient_timeout_feed_%’;

    delete from `wp_options` where `option_name` like ‘_transient_feed_%’;

    Post ReplyPost Reply
  1. #5 syndrael
    September 4, 2012 8:22 am

    “You can disable the post revision function by adding in the line as below to wp-config.php”
    But not at the end of the file but before:
    /** Sets up WordPress vars and included files. */
    require_once(ABSPATH . ‘wp-settings.php’);
    Best regards from Paris, France

    Post ReplyPost Reply
  1. #6 mazdodot
    October 3, 2012 4:44 am

    Great, worked for me, thank you mister.

    Post ReplyPost Reply
Leave a Comment