10+ useful SQL queries to clean up your WordPress database

by Jean. 18 Comments -

After years of usage, your WordPress database can contain weird characters, be filled with data you don’t need anymore, and so on. In this article, I’m going to show you 10+ SQL queries to clean up your WordPress database.

Two things to note: First, any of these queries should be preceded by a backup of your whole database. Secondly, don’t forget to replace the wp_ table prefix by the prefix used on your WordPress install, otherwise the queries won’t work.

Clean up your WordPress database from weird characters

Encoding problems can be really painful. Instead of manually update all of your posts, here is a query that you can run in order to clean your database from weird characters.

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');

UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '“');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '”', '”');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', '’');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', '‘');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '—', '–');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '–', '—');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '…');

→ Source: http://digwp.com/2011/07/clean-up-weird-characters-in-database

Close trackbacks on all posts at once

Do you use trackbacks and pings? Many people seems to find them useless. In order to get rid of them, you can close trackbacks post by post, but this will consume a lot of time. Or, of course, you can use a good old SQL query, as shown below:

UPDATE wp_posts SET ping_status = 'closed';

→ Source: http://www.wprecipes.com/wordpress-tip-close-trackbacks-on-all-posts-at-once

Get rid of all unused shortcodes

I love WordPress shortcodes, but there’s a problem with them: Once you stop using a shortcode (for example when you switch to another theme) you’ll find shortcodes in full text on your posts. Here’s a SQL query to remove them. Just update the code with the shortcode you want to remove. I’ve used [tweet] in this example.

UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' ) ;

→ Source: http://www.wprecipes.com/wordpress-tip-get-rid-of-unused-shortcodes

Delete specific post meta

If you used to add a specific custom field to your posts but do not need it anymore, you can remove the undesired meta quickly with this query.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

→ Source: http://www.esoftload.info/10-sql-statements-for-wordpress

Delete all unused post tags

Remember 4 or 5 years ago, tags where very popular in blogging. But now, many bloggers stopped used them. If you did, save some space on your database by cleaning it from unused tags.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

→ Source: http://4rapiddev.com/tips-and-tricks/wordpress-delete…

Delete feed cache

WordPress stores the feed cache in the wp_options table. If you want to flush the feed cache, you can do so by using the following query:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')

→ Source: http://wpengineer.com/2114/delete-all-feed-cache…

Delete all post revisions and their metadata

Post revisions is an useful feature, but if you don’t delete the many revisions from time to time your database will quickly become very big. The following query deletes all post revisions as well as all the metadata associated with the revisions.

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

→ Source: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries…

Batch delete old posts

Don’t need those posts published years ago? Delete them using this query. This example is set to delete any post which is older than 600 days. If you want to make an even better version of this query, what about mixing it with the one below to remove old posts as well as their metadata?

DELETE FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > 600

→ Source: http://stackoverflow.com/questions/5317599/wordpress-automatically-delete-posts…

Remove comment agent

By default, when someone comments on your blog, WordPress saves the user agent in the database. It can be useful for stats, but for 95% of bloggers it is just useless. This query will replace the user agent with a blank string, which can reduce your database size if you have lots of comments.

update wp_comments set comment_agent ='' ;

→ Source: http://www.rsatechnologies.in/best-sql-queries-for…

Batch disable all plugins

Sometimes, for exemple when you have to upgrade your blog, you need to disable all your plugins. Depending to how much plugins you’re using, it can takes a lot of time and be kinda boring. Here is an useful SQL query to disable all your plugins at once!

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

→ Source: http://www.wprecipes.com/how-to-disable-all-your-plugins…

Change author attribution on all posts at once

Do you need to change author attribution on many posts? If yes, you don’t have to do it manually. Here’s a handy query to do the job for you.

The first thing to do is getting the IDs of WordPress users. Once logged in phpmyadmin, insert the following SQL command:

SELECT ID, display_name FROM wp_users;

Right now, phpmyadmin displayed a list of WordPress users associated with their IDs. Let’s say that NEW_AUTHOR_ID is the ID of the “new” author, and OLD_AUTHOR_ID is the old author ID.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

That’s all. Once this last command has been run, all posts from the old author now appears to have been written by the new author.
→ Source: http://www.wprecipes.com/how-to-change-author-attribution…

Comments (18) - Leave yours

  1. Piet said:

    some very useful commands, thanks for sharing!

    can’t get “Delete all post revisions and their metadata” to work though, but given the fact that it is already from two years ago, maybe it has been replaced by sth else already…

    • FrancoisM said:

      Of course it doesn’t work, it’s wrongly written ! (And not even tested by the author BTW)

      Syntax should read:
      DELETE a,b,c
      FROM wp_posts a
      LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
      LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
      WHERE a.post_type = ‘revision’ ;

      HTH and be carefull with DELETE statements

      Francois

  2. Rob said:

    In the first query would it not make more sense to change the characters in to their html entities? just to stop any issues with rendering in the browser.

  3. Edward Lewis said:

    I think the one most people should use is the delete post revisions. I’m always suprised just how many times clients seem to go back and edit content and create soooo many versions of the same thing!

  4. Jenni said:

    Actually I don’t know why we need to use mySQL to batch disable all plugins while we can do it easily in the WordPress admin backend menu :(

  5. onyou said:

    nice post. thanks for sharing!

    I also use the following ones to get rid of pingbacks and spam comments:
    delete from wp_comments where comment_type=’pingback’;
    delete from wp_comments where comment_approved=’spam’ ;

  6. Damu said:

    How can i clean delete all posts, categories, comments. I imported from old version but failed halfway. I want to clean up before next attempt. i used ‘mass delete’ plugin but when i checked database all posts were there. WP version: 3.3.2

  7. Tim Nicholson said:

    Thanks for the post about the weird characters. There is certainly no easy way to switch character sets in MySQL. When I migrated servers I ended up having to set WP to latin1. This SQL helped me clean that up and set it back to utf-8.

    However, you need to do all this with the wp_posts post_title and post_excerpt fields as well.

    I also had to set my browser to latin1 in order to be able to copy-and-paste the code into phpmyadmin and get it to work. Otherwise, there were no errors reported, but it didn’t make any changes to the database either.

    I also had one other character that was an issue. The latin1 character equivalent to   I had to fix that as well.

    I hope this helps anyone else that might be trying to fix their database.

    On another topic here, I have added an option to my wp-config.php file to limit the number of post revisions to 1. That way I don’t have to worry about using SQL to get rid of tons of post revisions. Its as simple as adding this:

    /** Set number of post revisions stored **/
    define(‘WP_POST_REVISIONS’, 1);

    I’m going to try to delete unused tags next :-) Thanks for posting all this for us.

  8. Ravi said:

    Hi,

    You query did not work it was failing. I just modified it little
    DELETE a,b,c
    FROM wp_posts a, wp_term_relationships b, wp_postmeta c
    WHERE a.post_type = ‘revision’
    and a.ID = b.object_id and a.ID = c.post_id;

    And it worked. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please respect the following rules: No advertising, no spam, no keyword in name field. Thank you!