Logo Background

8 Useful SQL To Clean Up WordPress Database

  • By on September 6, 2011 | 1 Comment

    Looking for useful SQL script to clean up or housekeep your WordPress database? This article will focus on deleting post revisions, old spam comments and plugins RSS feed.

    WordPress database maintenance is a must to keep your blog running in better speed. Here are the 8 useful SQL that you can use to clean up your WordPress database easily and quickly.

    Delete WordPress Post Revisions And Metadata

    There’s one plugin called WP Optimize can handle the deletion of WordPress post revision but I prefer to execute the SQL scripts as below to reduce the size of WordPress database.

    DELETE FROM wp_term_relationships
    WHERE NOT EXISTS 
    (
            SELECT * FROM wp_posts
            WHERE wp_term_relationships.object_id = wp_posts.id
    );
    DELETE wp_postmeta FROM wp_postmeta
    WHERE NOT EXISTS 
    (
            SELECT * FROM wp_posts
            WHERE wp_postmeta.post_id = wp_posts.id
    );

    Delete Unnecessary Plugin RSS Feed

    This is the best part of the WordPress database clean up. WP_Options is one of the table that stores the plugin configuration or setting.

    From time to time, I notice that WP_Options table grow enormously and it became the largest table in the WordPress database.

    After several investigation, I found out that the WP_Options table stores the RSS feed for plugin installed in WordPress.

    Do a quick check on your WordPress Dashboard and remove those plugin feed that appears in it.

    And then follow by the execution of the WP_Options clean up script as below.

    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_%';

    I manage to keep the WP_Options below 100K in size after the clean up and the performance of the WordPress blog has increase tremendously.

    Delete Old Spam Comments And Metadata

    Check this two SQL clean up script if you have Akismet plugin installed in your WordPress.

    The first one will clean up wp_commentmeta entries which have no relation to wp_comments.

    While the second one will clean up Akismet related metadata in wp_commentmeta table.

    DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
    DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

    Rememeber to create a backup on your WordPress database before execute any of the clean up script as above.

    What other ways do you use to make your WordPress database slimmer?

    Previous
    Next
    » Why Clean Up Wordpress WP_PostMeta Table?
  1. #1 Jonas
    July 6, 2012 3:28 pm

    Hi!

    This was just what I was looking for, this helped clean my database a whole lot. Especially those _transient_% entries took a lot of space. They keep coming back though. Is there a way to disable it completely? Also the _edit_last and _edit_lock.

    Thank you!

    Post ReplyPost Reply
Leave a Comment