15 Best SQL Queries for wordpress database Administrator

Do not run these SQL queries before taking the backup of your current database. How to take backup of you wordpress database is covered here in this tutorial.

To take backup of your Database you can use WP-DB-Backup or WP-DBManager plugins from the wordpress plugins repository. Alternatively you can use phpMyAdmin

An easy way to run any of the queries below is to use phpMyAdmin.  phpMyAdmin is one of the most common database look-up and editing tool. Almost all the web hosting services’ control panels provide this. There is also WordPress SQL Executioner – a WordPress plugin that allow you to execute your SQL query.

General Purpose SQL Queries for wordpress

Problem : Change Default Administrator Name

By default every wordPress installation creates an account with a default “Admin” username. This is wide spread knowledge, everyone who uses WordPress knows this. However, this can be a security issue because a hacker can use brute force your WordPress admin panel. A different default “Admin” username, means additional security.

Solution:

UPDATE p_users SET user_login = 'NewName' WHERE user_login = 'Admin';

Problem : How to Reset user password

Forget your wordpress password or want to reset your password in WordPress, and do’t want to use password reset.php file to upload on your server.

Solution: To reset user Password

update wp_users set user_pass =md5(‘newpassword’) where user_login=’yourusername’);

Problem : How to Delete Post meta

Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of the post meta to store data related to the plugin. After you have removed the plugin, those data are 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 : Remove Meta Tags

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

Problem : How to Delete All pingback

Some wordpress administrator does not like pingback on their blog. Suppose now you have disabled pingback on your wordress blog. but what about your stored pingback, There is no method available in wordpress to delete the records. Here is the solution from SQL queries to fight this menace.

Solution: Delete all PingBack

delete from wp_comments where comment_type=’pingback’;

Problem : Delete all spam comments from the database

Spam comments and wordpress are two inseparable terms. Doesn’t matter how many methods you apply to fight spam, they will come up every time. So how to delete all these comment from the database use SQL queries

Solution:

delete from wp_comments where comment_approved=’spam’ ;

Problem : How to Delete all trash Comments from the database

There are simple methods now available in new wordpress 3.0 onwards , if you are still feel comfortable with SQL queries . Issue the following command and delete all  the trash comments form the database forever.

Solution: Delete all trash comments from the Database

delete from wp_comments where comment_approved=’trash’ ;

Problem : How to remove comment_agent

Comment agent is used to hold information of the commentator’s browser , operating system etc, most of the time this is use less, so how to remove this unimportant data from the database

Solution: Remove Comment Agent

update wp_comment set comment_agent =’ ’ ;

Problem : How to Delete all revision posts from the database

When ever we revise any post on our wordpress blog it creates a same copy of the post with it’s post type as revision. The original post which is now current does not require all these post to show your current post. So if you have revised an article 20 times., it means your post has been saved 19 more times, Thus increase a lots of web-space on your server.

Below listed SQL query  will delete all the these revised posts from the database. leaving your original ( Latest ) contents

Solution:

delete from wp_posts where post_type=’revision’ ;

Problem : How to Find out all Unique Email Address

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 : To find out Unique Email Address

select DISTINCT comment_author_email FROM wp_comments;

Problem : How to Identifying Unused Tags

In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to identify the unused tags.

Solution:

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.coun

Migration Specific SQL Queries for wordpress

Problem : How to Change SITE URL  and HOME URL

WordPress stores the absolute path of the site URL and home URL in the database. Therefore, if you 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.

Solution:

update wp_options set option_value =’http://yourwebsiteurl.com’ where option_name=’siteurl’ or option_name =’http://yoursiteurl.com’;

Problem : How to Change GUID

After you have migrated your blog from the localhost to your server or from another domain to a new domain, 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.

Solution:

update wp_posts set guid=replace(guid,’http://oldsite.com’,’http://newsite.com’);

Problem : How to change URL in Post-contents

WordPress uses absolute path in the URL link instead of a relative path in the URL link when storing them in the database. Within the content of each post record, it stores all the old URLs referencing the old source. Therefore you will need to change all these URLs to the new domain location.

Solution :

update wp_posts set post_content= replace(post_content,’http://oldsitename.com’,’https://binarynote.com’)

Problem : How to Change Image Path Only in Post Content

By default wordpress store absolute path of the relative images and this create a lots of problem whenever we change our server. SO how to change the image paths stored in the post contents

Solution:

update wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsit.com','src="http://newsite.com');

The above query only change the source of images not the attachments available with that post, So issue the following query also along with the above one.

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

Problem : How to Update/Change  Post Meta

Post meta is also stored as the same way as your post image and other URLs ie, absolute path, so change them according to new website address

Solution:

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

Your Turn

Though, I have tried my best to collect these SQL queries to help wordpress administrator/webmaster, if you find any problem or better solution for these aforesaid problem, kindly drop your message here so that we can update.

Similar Articles You may Like