As you may already know, WordPress stores a lot of non-used information within your MySQL database. This can be things like posts, pages, comments, shortcodes, plugin settings and a lot more. The issue here though is that with such a large database that will always be growing in size, you need to make changes and optimise and clean your database. If we were to clean up a database via the CMS dashboard, this can become a very time-consuming task which is why we made this article today. We are going to give you some very useful time-saving WordPress SQL Snippets to help clean up your website!
Before we start…
MAKE A BACKUP!.
Mistakes can happen, and the database is a very important thing, let’s ensure we can recover the site if a mistake is made. The best way would be to download a backup of your site then do the changes locally, you don’t want your live website to go down right? If you aren’t quite sure how to make a backup, you can read the following article.
Now we have the backup made, let’s dig into these awesome queries!
1. Delete WordPress Post Meta
When you install a WordPress Plugin, they make use of the post meta table to store data. When you remove the plugin, the data will actually still remain in your post_meta table even though it’s no longer being used! We can remove this by running the following SQL query:
DELETE FROM wp_postmeta WHERE meta_key = 'MyMetaKey';
Just change MyMetaKey to your own value before running this query.
2. Identify Unused WordPress Tags
If you run a query to delete old posts (like the one we did above), the old tags will remain within the database. The following query allows you to identify all of the unused tags:
SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
Just change post_tag to the ones you are wanting to remove.
3. Delete WordPress Spam Comments in Bulk
This snippet we’ve used quite a lot of times and it really does save time. You can simply delete WordPress spam comments in bulk by running the below query:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
4. Batch Delete All Unapproved WordPress Comments
The following SQL snippet will remove all thee unapproved comments without effecting the ones which are approved:
DELETE FROM wp_comments WHERE comment_approved = 0
5. Disable WordPress Comments on Old Posts
For this query, just adjust the comment_status to either open, closed, or registered only. Now, all you have to do is specify the date by editing the value 2017-01-01 to the date in which you want to delete posts from.
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2017-01-01' AND post_status = 'publish';
This query will delete all the posts when the post data is less than 1st January 2017.
6. Disabling & Enabling WordPress Trackbacks & Pingbacks
In this one, we just need to define the code>comment_status as either open, closed, or registered_only.
To globally enable pingbacks/trackbacks for all users, we can use:
UPDATE wp_posts SET ping_status = 'open';
To globally disable pingbacks/trackbacks for all users, we can use:
UPDATE wp_posts SET ping_status = 'closed';
Furthermore, we can specify the ping_status as either open or closed. But, also specify the date by editing the 2017-01-01 to suit your needs:
%8. Select & Delete WordPress Posts that are over 'X' Days Old
If you ever need to select and delete posts that are over a certain amount of days old, this snippet will help for sure!
To select any posts that are over 'X' amount of day, run this query. Remember to replace the X with the number of days you are looking for:
<xmp> SELECT * FROM 'wp_posts' WHERE 'post_type' = 'post' AND DATEDIFF(NOW(), 'post_date') > X </xmp>
Once you are happy with the results of this, you can then safely delete them as follows:
<xmp> DELETE FROM 'wp_posts' WHERE 'post_type' = 'post' AND DATEDIFF(NOW(), 'post_date') > X </xmp>
We simply replaced the SELECT * to the DELETE command.
9. Removing Unwanted WordPress Shortcodes
WordPress shortcodes are a great tool to use, but when you decide to stop using them, they can remain within your post content...
With this query, we can remove any unwanted shortcodes, simple replace notusedcode with the shortcode you wish to remove:
<xmp> UPDATE wp_post SET post_content = replace(post_content, '[notusedcode]', '' ) ; </xmp>
10. Change Author Attribution On All WordPress Posts
Ever built the website then the author of the site wanted to replace your user ID with their own? This can be done in several ways but a simple SQL query can resolve this quite quickly. Firstly, we will need to find the User ID as follows:
<xmp> SELECT ID, display_name FROM wp_users; </xmp>
Once you have found the old ID and the new ID in which you want to replace, you can run the following command:
<xmp> UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID; </xmp>
Replace NEW_AUTHOR_ID with the ID of the user to take the new ownership, and set OLD_AUTHOR_ID which is the current owner.
11. Batch Deleting WordPress Post Revisions
Post revisions can be extremely handy, however, they also increase the size of your MySQL database. You can manually delete post revisions but this is a very time-consuming task. You can remove these using thing handy SQL query:
<xmp> DELETE FROM wp_posts WHERE post_type = "revision"; </xmp>
12. Disable or Enable All WordPress Plugins
If you've ever encountered the horrible white screen of death and found yourself unable to access the site or WordPress Admin panel after activating a new plugin. Then this little snippet will come of great use. This will disable all plugins instantly and will allow you to regain access:
<xmp>
UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';
</xmp>
13. Change the Destination URL of a WordPress Site
If you've decided to move your WordPress site manually from one server to another, the first thing you will need to do is tell WordPress the new URL of your website.
Remember and change http://www.old-site.com to your old URL, and the http://www.new-site.com to your new URL.
The first command to use is this one:
<xmp>UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-site.com', 'http://www.new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl'; </xmp>
Then you will have to change the URL from the table wp_posts with this snippet:
<xmp>UPDATE wp_posts SET guid = replace(guid, 'http://www.old-site.com','http://www.new-site.com); </xmp>
Finally, you'll need to do a search through the content of your posts to be sure that your new URL link is not still linked with any of the old URL references:
<xmp>UPDATE wp_posts SET post_content = replace(post_content, ' http://www.old-site.com ', ' http://www.new-site.com'); </xmp>
14. Manually Reset your WordPress Password
If you only have a single user on your WordPress installation, and the login name is 'admin' for example. You can reset your password with this simple SQL query. Once executed, it will replace PASSWORD with your new password.
<xmp>
UPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;
</xmp>
15. Search and Replace WordPress Post Content
To search and replace post content, use the following code. Just replace OriginalText with the current text and replace NewText with your new text.
<xmp>
UPDATE wp_posts SET 'post_content'
= REPLACE ('post_content',
'OriginalText',
'NewText');
</xmp>
16. Changing the URL of WordPress Images
If you need to change the paths of your images, you can run the following SQL command:
<xmp> UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com', 'src="http://www.mynewurl.com'); </xmp>
17. Change the Default 'Admin' WordPress Username
With every WordPress installation, there will be an account which has the default Admin username. By changing the default username, it will give your WordPress admin panel additional security. To do this, change YourNewUsername to your new name:
<xmp> UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin'; </xmp>
And that's all we have today. We're pretty sure this will save you a great deal of time and we hope it helps you!
