If you are using WordPress for a long time, it is possible that you have many spam comments, revisions, transients cache etc. on your wp database. In this post, I listed most useful SQL queries you can use to clean up your WordPress database and reduce its size around 85%!

Tools Required

My favorite tool for database cleanup is phpMyAdmin. If your hosting provider has CPanel, you can access your database using phpMyAdmin. If you don’t have CPanel, you can use a WordPress plugin to run SQL queries on your WordPress database.

Backup Your Database First

If you are going to run a manual query on WordPress database, I highly recommend you to backup your database first. Even when you think a query is harmless, a small mistake on the query can cause an unrecoverable damage on your tables.

1. Replace Old Links on Posts

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "http://shailan.com", "https://wpassist.me" ) WHERE `post_type`="post";

This snippet replaces all occurrences of a link on your post contents. I have added filterpost_type, so it will replace links only on posts.

2. Replace or Remove Old Shortcodes

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[html]", '<pre class="html">' ) WHERE `post_type`="post";
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[/html]", "</pre>" ) WHERE `post_type`="post";

This snippet replaces removed shortcodes with HTML tags. If you just want to remove the shortcode you can use the following sample:

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[adsense]", '' ) WHERE `post_type`="post";

3. Delete All Post Revisions

DELETE FROM `wp_posts` WHERE `post_type`="revision"

This query snippet removes all post revisions from the database. Please note that this code doesn’t remove any post meta or term relationships. See tip 11 for removing orphaned post meta.

4. Close Comments on All Posts

UPDATE `wp_posts` SET `comment_status` = 'closed' WHERE `post_type`="post";
UPDATE `wp_posts` SET `ping_status` = 'closed' WHERE `post_type`="post";

This query closes comments and pings for all posts on your WordPress blog. If you want to enable comments, you just need to change “closed” to “open”.

5. Delete All Trashed Posts

DELETE FROM `wp_posts` WHERE `post_status`="trash"

This SQL query snippet removes all trashed posts from your database. It can save you a good amount of memory if you have many posts.

6. Delete All oEmbed Cache

DELETE FROM `wp_posts` WHERE `post_type`="oembed_cache"

I recently disabled oEmbed on my WordPress blog. So, if you are not using oEmbeds, this is also unnecessary data in your database. This query will remove all oEmbed cache from your database.

7. Delete Old Contact Forms Shortcodes

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, '[contact-form-7 id="4339"]', '' ) WHERE `post_type`="post";

This query deletes all contact forms with the given id from your posts.

8. Delete All Pingbacks

DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback';

This snippet deletes all pingbacks from the comments table.

9. Delete All Spam Comments

DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam';

This query removes all spam comments from comments table.

10. Delete All Transients On Options Table

DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient%';

This query deletes all transient cache from your options table. This query can save you a huge amount of memory depending on your WordPress database age.

11. Delete All Orphaned Post Meta

DELETE m FROM `wp_postmeta` AS m
LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID`
WHERE p.`ID` IS NULL

This query deletes all post meta that is not linked to any post. This query needs to be run if you manually deleted post revisions or posts using SQL.

12. Get a Full List Of Meta Keys

SELECT DISTINCT meta_key FROM `wp_postmeta`

This is not a database cleanup query. But, once you get a list of meta keys that are added, you can use those keys to delete unused meta keys on your database. See next query tip for an example.

13. Delete Removed Plugin Meta Keys

DELETE FROM `wp_postmeta` WHERE `meta_key` LIKE '%aktt%'

This query deletes all meta keys including a keyword. If you have a word common to a plugin meta, you can use that keyword to remove all meta keys used by that plugin.

14. Update Post Author ID on All Posts

UPDATE `wp_posts` SET `post_author` = '1' WHERE `post_type`='post' AND `post_status`='publish'

This query doesn’t remove any rows, but updates author for all posts to a specified author ID. If you had guest authors in the past, you can use this query to remove all old authors.

BONUS: Trigger to Empty Trashed Posts on Post Publish

CREATE TRIGGER `EMPTY_TRASH_ON_PUBLISH` AFTER INSERT ON `wp_posts` FOR EACH ROW DELETE FROM `wp_posts` WHERE `post_status`="trash"

This query creates a trigger on your database to empty your deleted posts on post publish.

 

BONUS: One Query For All

I merged all cleanup queries above in one box, so you can run this directly on SQL field:

DELETE FROM `wp_posts` WHERE `post_type`="revision";
UPDATE `wp_posts` SET `comment_status` = 'closed' WHERE `post_type`="post";
UPDATE `wp_posts` SET `ping_status` = 'closed' WHERE `post_type`="post";
DELETE FROM `wp_posts` WHERE `post_status`="trash";
DELETE FROM `wp_posts` WHERE `post_type`="oembed_cache";
DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback';
DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam';
DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient%';
DELETE m FROM `wp_postmeta` AS m
LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID`
WHERE p.`ID` IS NULL;

I hope you found those queries useful. Follow me on twitter for more tips. Enjoy!

Deal Notice: We are using A2 hosting for all of our websites, and we are really happy with their service. So we wanted to share you this discount link for you to try it out. If you get a hosting using this link, you will also help us build better content for you: SSD Hosting - 300% Faster Than Standard Hard Drives