Knowledgebase

Please visit our channel & subscribe: https://youtube.com/dhakawebhost

 

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, I’m going to show you 10+ 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 install, otherwise the queries won’t work.

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.

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, '…', '…');

 

Close trackbacks on all posts at once

Do you use trackbacks and pings? Many people seems to find them useless. In order to get rid of them, you can close trackbacks post by post, but this will consume a lot of time. Or, of course, you can use a good old SQL query, as shown below:

UPDATE wp_posts SET ping_status = 'closed';

 

Get rid of all unused shortcodes

I love WordPress shortcodes, but there’s a problem with them: Once you stop using a shortcode (for example when you switch to another theme) you’ll find shortcodes in full text on your posts. 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]', '' ) ;

 

Delete specific post meta

If you used to add a specific custom field to your posts but do not need it anymore, you can remove the undesired meta quickly with this query.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

 

Delete all unused post tags

Remember 4 or 5 years ago, tags where very popular in blogging. But now, many bloggers stopped used 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_%')

 

Delete all post revisions and their metadata

Post revisions is an 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 post 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);

 

Batch delete old posts

Don’t need those posts published years ago? Delete them using this query. This example is set to delete any post which is older than 600 days. If you want to make an even better version of this query, what about mixing it with the one below 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 comments 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 comments.

update wp_comments set comment_agent ='' ;

 

Batch disable all plugins

Sometimes, for exemple when you have to upgrade your blog, you need to disable all your plugins. Depending to how much plugins you’re using, it can takes a lot of time and be kinda boring. Here is an useful SQL query to disable all your plugins at once!

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

 

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 getting the IDs of WordPress users. Once logged in phpmyadmin, insert the following SQL command:

SELECT ID, display_name FROM wp_users;

Right now, phpmyadmin displayed a list of WordPress users associated with their IDs. Let’s say that NEW_AUTHOR_ID is the ID of the “new” author, and OLD_AUTHOR_ID is the old author ID.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

That’s all. Once this last command has been run, all posts from the old author now appears to have been written by the new author.