After years of usage, your WordPress database can contain weird characters, be filled with data you don’t need anymore, and so on. In this article, you will learn about SQL queries to clean up your WordPress database.
Two things to note: First, any of these queries should be preceded by a backup of your whole database. Secondly, don’t forget to replace the wp_ table prefix by the prefix used on your WordPress website install, otherwise, the queries won’t work.
How to Run SQL Queries on Your WordPress Database
Before getting into the examples, let’s take a moment to check out how it is possible to run SQL queries on a WordPress website. You have three possibilities:
- Using SSH: If your WordPress hosting allows SSH connections, you can simply connect to your server and run the queries directly into your MySQL database.
- Using PHPMyAdmin: Most WordPress hosting packages come with cPanel and PHPMyAdmin, a web interface that allows you to execute SQL queries.
- Using a WordPress plugin: Database My Admin is a WordPress plugin that allows you to run any SQL queries against your WordPress database from within your WP dashboard. If you don’t want to manually run queries and just need to optimize your database, Advanced Database Cleaner might be a plugin to consider.
Clean up Your WordPress Database From Weird Characters
Encoding problems can be really painful. Instead of manually update all of your posts, here is a query that you can run in order to clean your database from weird characters. Your WordPress site will be much more enjoyable to read for your visitors.
UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '"');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€', '"');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', ''');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', ''');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€"', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€"', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '...');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '"');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€', '"');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', ''');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', ''');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€"', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€"', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '...');
Source: http://digwp.com/2011/07/clean-up-weird-characters-in-database
Reset Administrator Password
WordPress security isn’t something to neglect, and passwords should be changed every once in a while to make sure that your WordPress website stays secure.
As user passwords are stored within the database, it is possible to reset them using a simple SQL query. Simply modify the query below by replacing admin_username by the username of which you want to change the password. new_password is the desired updated password.
UPDATE `wp_users` SET `user_pass` = MD5( 'new_password' ) WHERE `wp_users`.`user_login` = "admin_username";
Note the use of MySQL’s MD5 function, which creates an MD5 hash of the specified password. WordPress security standards require that passwords are stored in the database as MD5 hashes.
Update Links to HTTPS
If you recently switched your WordPress website or blog to HTTPS, you need to update hardcoded links within your articles. This is a tedious task if you do it manually, but it will take you less than a minute if you use SQL queries to update all links contained within your content.
Simply update the query below by replacing yoursite.com by your URL, and run it.
UPDATE wp_posts SET post_content = replace(post_content, 'http://yoursite.com', 'https://yoursite.com');
Close Trackbacks on All Posts at Once
Do you use trackbacks and pings? Nowadays, most people seem to find them useless. In order to get rid of them, you can close trackbacks manually, but this will consume a lot of time. Or, of course, you can use a good old SQL query to perform a database cleaning, as shown below:
UPDATE wp_posts SET ping_status = 'closed';
Mass Delete All Spam Comments
Spam is extremely common, and if you chose to give your readers the ability to interact with your articles, there’s no doubt that a lot of spam will be received.
Over the years, WordPress has drastically improved the way spam is handled. If spam is detected, it isn’t displayed on your WordPress site straight away, but instead, it’s kept in a queue where you can choose whether to approve them or not.
If your spam queue is long, the fastest way to mass delete all spam comments is to run the following SQL query:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Get Rid of All Unused Shortcodes
WordPress shortcodes are very useful and make it easy to embed info in your articles without having to modify any of your WordPress themes. Nowadays, a wide array of WordPress plugins offer shortcodes that can be used to integrate data within the WordPress editor.
But unused shortcodes can create readability problems: Once you stop using a shortcode (for example when you switch to another WordPress theme) you’ll find shortcodes in full text within your content. Here’s a SQL query to remove them. Just update the code with the shortcode you want to remove. I’ve used [tweet] in this example.
UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' ) ;
Source: http://www.wprecipes.com/wordpress-tip-get-rid-of-unused-shortcodes
Delete Specific Post Meta
Post meta is data associated with a specific post. For example, when you create a custom field, the data is stored as meta. It can then be retrieved and displayed on your WordPress website.
If you used to add a specific custom field to your posts but do not need it anymore, you can perform this “database cleaner” query and remove the undesired post meta quickly and effortlessly.
DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';
Source: http://www.esoftload.info/10-sql-statements-for-wordpress
Delete All Unused Tags
A decade ago, tags were very popular in blogging. Nowadays, most bloggers and WordPress site owners stopped using them. If you did, save some space on your database by cleaning it from unused tags.
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Delete Feed Cache
WordPress stores the feed cache in the wp_options table. If you want to flush the feed cache, you can do so by using the following query:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')
Source: http://wpengineer.com/2114/delete-all-feed-cache…
Optimize Your WordPress Database by Removing Transients
WordPress transients are basically a caching feature: They are used to store any kind of data that takes a long time to get, and therefore are returned super fast the next time you need it.
While this is definitely a super useful feature, transients can take a lot of space in your database when left unmanaged, and reduce your WordPress website performance.
To perform an advanced database cleanup, use the query below:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');
It is totally safe to remove WordPress transients from time to time, as WordPress will recreate the needed transients.
Delete All Revisions and Their Metadata
Revisions are a very useful feature, but if you don’t delete the many revisions from time to time your database will quickly become very big. The following query deletes all revisions as well as all the metadata associated with the revisions.
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
Source: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries…
Batch Delete Old Posts
Sometimes, you might need to delete very old articles that are no longer relevant. The following query will delete any article older than 600 days. This value (defined on line 3) can be replaced by any desired date in days.
If you want to make an even better version of this query, what about mixing it with the previous one in order to remove old posts as well as their metadata?
DELETE FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > 600
Remove Comment Agent
By default, when someone leaves a reply on your blog, WordPress saves the user agent in the database. It can be useful for stats, but for 95% of bloggers, it is just useless. This query will replace the user agent with a blank string, which can reduce your database size if you have lots of replies.
update wp_comments set comment_agent ='' ;
Update Admin Email Address
All WordPress data is stored within database tables, which means that it can very easily be updated using SQL queries.
If you need to update the admin email, here is the query to do it.
UPDATE `wp_users` SET `user_email` = "new_email_address" WHERE `wp_users`.`user_login` = "admin";
Of course, this query can be used to update any email contained within your wp_users table. Simply replace admin with the username of the account you want to change the email address.
Batch Disable All WordPress Plugins
It can happen that a faulty plugin breaks your WordPress site. Even worst, depending on how serious the error is, you can end up being unable to access your wp_admin area where you could have been able to deactivate the WordPress plugin causing an error.
In that case, the best thing to do is to deactivate all the plugins using the following SQL query:
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
Most likely, this will solve the error and allow you to log back into your WP dashboard, where you can re-activate plugins one by one and identify which one was causing problems.
Switch WordPress Themes using SQL
WordPress stores your site settings within the wp_options database table. Therefore, your active WordPress theme can be modified using a simple SQL query.
This can be very useful if your active theme has an error that prevents you from accessing your admin dashboard. The following query will restore WordPress’ Twenty Nineteen as the active theme.
UPDATE wp_options SET option_value = 'twentynineteen' WHERE option_name = 'template' or option_name = 'stylesheet';
Change Author Attribution on All Posts at Once
Do you need to change author attribution on many posts? If yes, you don’t have to do it manually. Here’s a handy query to do the job for you.
The first thing to do is to retrieve the IDs of WordPress users. Once logged into MySQL, use the following SQL query to get a list of users, as well as their IDs:
SELECT ID, display_name FROM wp_users;
Let’s consider that NEW_AUTHOR_ID is the ID of the new author, and OLD_AUTHOR_ID is the old author ID. Run this query to assign a new author to all articles currently assigned to OLD_AUTHOR_ID.
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
Once this query has been executed, all posts from the old author now appear to have been written by the new author.