Maintaining your WordPress Blog can be a labouring task if you are looking for fields manually in your database and it’s almost impossible if your blog has 1000′s of posts. Redundant data takes up space in your database and can also slow down your website!
For example, i have a blog with 30,000 daily visitors and over 40,000 posts and in order to maintain that blog i have to run certain queries on a weekly basis to ensure my database is not overloaded with useless data as WordPress has many long slow queries when posting which require full table scans and the smaller your database the faster it will run.
Backup Your Database
Before you run any SQL Queries on your Database it is 100% recommended that you make a backup!
You can backup your database with WordPress Plugins such as WP-DBManager and WP-DB-Backup or through phpMyAdmin directly.
If you decide to backup your WordPress database manually, follow these steps:
- Login to your phpMyAdmin.
- Select your WordPress database.
- Click on Export at the top of the navigation.
- Select the tables you want to backup, or select all tables to backup the whole database.
- Select SQL to export as .sql extension.
- Check the “Save as file” checkbox.
- Choose compression type, select gzipped to compress the database to a smaller size.
- Finally click Go, and a download window will prompt you to save your backup database file.
14 WordPress SQL Queries
In order to run any of these SQL Queries you need access to phpMyAdmin or you can use WordPress SQL Executioner which allows you to run SQL Queries on your database through WordPress.
In no particular order…
1. Change Your Website URL and Home URL
WordPress stores your Website URL and HOME URL in the database to set an absolute path, if for example you are changing domain names you will need to alter these fields and you can do this with an SQL Query.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.originalsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';
2. Change your Website GUID
If you are moving your Blog to a new domain you will need to update the GUID field in wp_posts. This is a crucial change as it sets the absolute path for your posts and pages.
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.originalsiteurl.com', 'http://www.newsiteurl.com');
3. Change Domain URL in Content
If you are changing domains then the chances are your posts will contain URLs for your old domain but not to worry, you can change all those URLs with this query.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.originalsiteurl.com', 'http://www.newsiteurl.com');
4. Change Your WordPress Password
Forgetting a Password is very common and can be quite frustrating but you can soon change your WordPress Password with a simple query. This query also turns your password into a MD5 hash which offers better protection. You can also change the password for other users on your blog, just replace “admin” with their username.
UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin'
5. Batch Delete Spam Comments
Spam comments can be an absolute nightmare on your blog especially if you are on holiday for example and you come back to find 100′s of random comments. You can delete all of these unapproved comments with a simple query.
DELETE from wp_comments WHERE comment_approved = '0';
6. Batch Delete Posts
On my most popular blog i had 40,000 posts where at least 15,000 were old and pointless now so i had to remove them, if i were to do this manually it would take all day but luckily there is a query. Please take care when entering the dates replacing “2010-01-01″, once posts re deleted you can’t get them back unless you have a database backup!
DELETE FROM wp_posts WHERE post_date < '2010-01-01 19:18:00' AND post_status = 'publish'
7. Delete Redundant Tags
If you have lots of tags that aren’t linked to any posts e.g after you have deleted posts, then you can remove all non-linked tags with this query. Simply replace “database_name” with the name of your database and then run the query.
DELETE a,c FROM database_name.wp_terms AS a LEFT JOIN database_name.wp_term_taxonomy AS c ON a.term_id = c.term_id LEFT JOIN database_name.wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id WHERE ( c.taxonomy = 'post_tag' AND c.count = 0 )
8. Delete Redundant Post Meta
Data related to posts can take up a lot of space in your database from plugins such as Post Ratings, Post Views etc, but once you delete a post that Post Meta is still stored in the database taking up space, use the following query to remove this useless data.
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
9. Disable All Plugins At Once
If for some reason you need to disable all your WordPress Plugins then the following query is for you.
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
10. Disable Comments On Old Posts
If you run a blog like this one where you like to communicate with your posters on recent posts and struggle to find time to reply to comments on your old posts then you can use this query to disable comments on posts before a certain period. Simply replace 2009-01-01 with a preferred date.
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2009-01-01' AND post_status = 'publish';
11. Delete Comments With A Specific URL
If you have spam comments that all contain the same URL then this query allows you to remove them in one go. The following query will delete all comments with a specific url. The “%” signs means that any url containing the string within the % signs will be deleted.
DELETE from wp_comments WHERE comment_author_url LIKE "%wpbeginner%" ;
12. Transfer Posts from User A to User B
If you have different posters on your blog and they decide to leave, you can simply remove all their posts to another account (ie yours) with this query. You will need the IDs of both the old and new author to do this.
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
13. Export All Commentors Emails Without Duplicates
Over time your blog with get comments and from these comments WordPress stores the emails which you can export for email campaigns. Once you have the result, under Query results operations, select export to export all the emails in phpMyAdmin.
SELECT DISTINCT comment_author_email FROM wp_comments;
14. Delete Pingbacks
Some of your popular articles will get many pingbacks and these can take up space in your database, you can delete all the records with this query.
DELETE FROM wp_comments WHERE comment_type = 'pingback';
Start Tidying Your Blog Today!
I can’t tell you how many times these SQL Queries have saved me in the past on some of my larger blogs so enjoy!
Nick
Comments