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%!
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://metinsaylan.com" ) WHERE `post_type`="post";
This snippet replaces all occurrences of a link on your post contents. I have added filter
post_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.
I hope you found those queries useful. Follow me on twitter for more tips. Enjoy!