15 Best SQL Queries for wordpress database Administrator
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.
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
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
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.
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.
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.
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.
update wp_posts set post_content= replace(post_content,’http://oldsitename.com’,’http://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
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.
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
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.