Whenever I work with WordPress and needed SQL hacks, have found this article very helpful. True said that there are cases when it becomes inevitable to run SQL queries directly to get things done despite of availability of many plugins.
Here are collection of my favorite WordPress SQL hacks.
1. Batch Delete Post Revisions:
Post revisions are very useful but they also increase the size of your MySQL database. You can manually delete revisions, but that’s very long and boring work.
SQL:
DELETE FROM wp_posts WHERE post_type = "revision";
2. Erase all Spam Comments:
Even though I have differnt plugins like AKismet, reCaptcha; I’ve always had very hard times dealing with spam. This solution made me very happy when I first used.
SQL:
DELETE FROM wp_comments WHERE comment_approved = '0';
3. Manually Reset Your admin Password:
SQL:
UPDATE `wp_users` SET `user_pass` = MD5('mypassword') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;
4. Change Your WordPress Address:
I often use this hack very often. There are many cases when project development is done in demo site address. When we need to make it live; there’s already lots of data (testing + live) and its necessary to run following SQL to get things going.
4.a. changes WordPress URL:
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
4.b. Replace the relative URL (guid) of each post:
UPDATE wp_posts SET guid = REPLACE(guid, 'http://www.oldsite.com','http://www.newsite.com');
4.c. Search and replace in the wp_posts table to make sure that no absolute URL is still here:
UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
We’re done changing WordPress Address. I am sure you do have your own collection of fav SQL hacks.
