Home > Programming Concepts > Most Useful MySQL Queries for WordPress

Most Useful MySQL Queries for WordPress

Backend of the most popular blogging CMS WordPress is handled by the MySQL, which is a relational database management system available free of cost to use. All data of your blogs like posts, comments, user information, settings etc. are saved in MySQL database attached to your blog at the backend of your hosting provider. Sometimes you need to modify your WordPress configuration which seem to be complicated by nature but can be handled easily via MySQL queries. To give some help about customizing your WordPress blog from backend, we are giving 10 useful MySQL queries, you are required to use database look-up tool – phpMyAdmin to run these queries. This post was written in partnership with sticker printing, an online printing company that provides quality printing services.

 

1. Assign all articles from one author to Another

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. Then use the following query to change the author of your articles.

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

 

2. Change Default “admin” Username

Every default WordPress installation will create an account with a default Admin username. If you can change your default “Admin” username, you will give your WordPress admin panel additional security.

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

 

3. Delete Revision

post revisions are the 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. To solve the problem and reduce the overhead of your database use the following query to delete post revisions.

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'

 

4. Change GUID

At the time of blog migration, you will need to fix the URLs for the GUID 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.

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

 

5. Change Siteurl & Homeurl

If you’ve planned to transfer your WordPress site from the localhost to your server, your site will not load 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.

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

 

6. Delete all Pingback

Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.

DELETE FROM wp_comments WHERE comment_type = 'pingback';

 

7. Change Image Path Only

If you’re using CDN to offload the delivery of images from your server. After your have created your CNAME record, you can use the query below to change all the image paths in WordPress to load all your images from CDN.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.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.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

 

8. Identify Unused Tags

While deleting any post does not guarantee the deletion of used meta tags, you have to do it manual, but use this query to find out which tags are not assigned to any post in your blog –

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;

 

9. Reset Password

Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason is but you can use following query to reset your password.

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

 

10. Update Post Meta

If you have stored extra URL data for each post, you can use the follow query to change all of them.

UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');

 
You are advised to make backup of your database and your WordPress site before attempting any of above mentioned MySQL queries. These queries have been tested although but precaution is the best way to avoid any damage.

I am Samith Jhon a content writer and a Professional Blogger. Certified with 1Z0-533 Exam test questions which is very
popular these days and have a great scope in the field of IT Certification. I always like to take 1Z0-516 Exam test questions to pass the Exam. These kinds of Exam questions could secure your future as well as your job.

a039f62aed73dbb8ba3609bba1bf99b4
Share via email Share

About amitrko

An Internet World Geek, hooked on all things pertaining to Web Design and Development & Online Magazine, intent on delivering you the best in Web content to simplify your Blogging Experience.

Leave a Reply