WordPress is driven by a MySQL database which makes updating content cake once in the database. All of the WordPress data such as posts, comments, categories, and settings are stored within the MySQL database.

If you needed to change some information site wide, going through each and every record would prove a very time consuming task and also be prone to human error. By executing a SQL query against your WordPress database however, you can make the necessary changes quickly and efficiently.

We will show some SQL queries that can be of great assistance to you when using WordPress.

While the tips in the rest of this post have been tested, you should definitely not try any of them without first having a proper backup of your MySQL database. Before you proceed with any changes, be sure to backup your database. It is a good practice to always backup your database before making any major changes. This ensures that even if anything were to go wrong, you would still be able to restore your data.

1. Change Siteurl & Homeurl

WordPress stores the absolute path of the site URL and home URL in the database. This technique would commonly be used following moving domain names or attempting to transfer your WordPress site from the localhost to your live server.

If you simply copy over your database, your site will not load properly online. This is because the absolute path URL is still pointing to your localhost. You will need to change the site URL and the home URL in order for the site to work.

Solution:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl.com', 'http://www.newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

2. Change GUID

After you have migrated your blog from the old domain to a new domain, you will need to fix the URLs for the GUID (globally unique identifier) field in wp_posts table. This is crucial because GUID is used to translate your post or page slug to the correct article absolute path if it is entered wrongly.

Solution:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://www.newurl.com');

3. Change URL in Content

WordPress uses an absolute path in the URL instead of a relative path in the URL link when storing them in the database. The problem comes when moving your site to a new server any links within pages/posts will link to your old domain/URL.

Therefore you will need to change all these URLs within existing posts so that they use the new domain location.

Solution:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldurl.com', 'http://www.newurl.com');

4. Change Image Path Only

This technique is most useful if you decide to use a service such as Amazon CloudFront as your Content Delivery Network (CDN) to offload the delivery of images from your server.

After your have created your CNAME record, you can change all the image paths in WordPress to load all your images from Amazon CloudFront instead.

Solution:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldurl.com', 'src="http://yourcdn. newurl.com');

You will also need to update the GUID for Image Attachment with the following query:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://yourcdn. newurl.com') WHERE post_type = 'attachment';

5. Change Default “Admin” Username

Every default WordPress installation will create an account with a default Admin username. This can be a security risk as a hacker can brute force attack your WordPress admin panel. If you can change your default “Admin” username, you will give your WordPress admin panel additional security to prevent against such an attack.

Solution:

UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';

6. Reset Password

Ever wanted to reset your password in WordPress, but the normal methods won’t do?

Solution:

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';

7. Update all articles by Author B to Author A

If you want to transfer all articles written under Author B to merge with those under Author A, it will be very time consuming if you try to go article by article. You can easily go through all the records and assign articles by Author B to go under Author A using MySQL.

You will first need to obtain the author ID of both authors by going to your Author & User page in your WordPress admin panel. Click on the author’s name to view their profile. At the address bar, look for “user_id”. That is the author ID information we require.

Solution:

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

8. Delete Revision

When you are editing an article in WordPress, there will be many revision copies being saved. This is a waste of resources because excessive revision records can increase the burden of the database. Over time, when you have thousands of entries, your database will have grown significantly. This will increase loop iterations, data retrieval and will affect the page loading time.

Solution:

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


9. Export all Comment Emails with no Duplicate

Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.

Solution:

SELECT DISTINCT comment_author_email FROM wp_comments;

10. Erase Every Spam Comment in a Second

The wp_comments table contains a field named comment_approved, which is a boolean value (1 or 0). Approved comments have a value of 1, and comments awaiting moderation have a value of 0. By running this SQL command, we simply delete any comments that haven’t been approved yet.

Solution:

DELETE from wp_comments WHERE comment_approved = '0';

11. Delete Post Meta

Installing or removing WordPress plugins is a very common task. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, there remains data still left inside the post_meta table, which will no longer be needed.

Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.

Solution:

DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

Victory!

If you need to do some maintenance and clean up or move WordPress across domains, the above SQL queries can be useful and help speed things up for you. If you have any additional handy SQL queries for WordPress, please share it with us below.