Some useful mySql Query for WordPress database

Here some Some useful mySql Query for WordPress database you shold know while working on WordPress database project.

Optimize the Database

As you work on your blog, your database transactions pile up overheads which can be a performance hog. To clear such overheads and optimize your database, use this query:

OPTIMIZE TABLE ‘wp_posts’;

Transfer Posts From One User to Another

If you wish to transfer posts and articles attributed to one user account to another account, you can use the following query (you’ll need the usernames of both the accounts):

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Bulk Delete Comments

You can use this query to delete all unapproved comments.

DELETE FROM wp_comments WHERE comment_approved=’0’;

Disable All Plugins

Perhaps you’ve accidently activated too many plugins? Or maybe you wish to make certain changes to your website and would like to disable the plugins? You can use the following query to disable all the plugins on your WP website:

UPDATE wp_options SET option_value=’ ‘ WHERE option_name=’active_plugins’;

Disable Comments on Older Posts

If you wish to close comments on posts older than a specific date (say, May 5th 2012), use the following query:

UPDATE wp_posts SET comment_status=’closed’ WHERE post_date<’2012-05-05′ AND post_status=’publish’;

Change Username

If you wish to change the username of a given user (say, ‘abcdef’), use this query:

UPDATE wp_users SET user_login=’New-username’ WHERE user_login=’abcdef’;

Change Default Administrator Name

UPDATE p_users SET user_login = ‘NewName’ WHERE user_login = ‘Admin’;

How To Change SITE URL  And HOME URL

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

Change Image Path Only In Post Content

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

Reset Password

Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason?

UPDATEwp_users SETuser_pass = MD5( 'new_password') WHEREuser_login = 'your-username';

Transfer posts from one user to another

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;