Today we are going to focus on SQL sentences to clean WordPress, because lately they are entrusting me with the task of optimizing page speed and cleaning the database becomes a crucial task.
It is clear that there are plugins that do the same thing that I will comment here, but we will go much faster if we do it directly from the phpMyAdmin or from the mySQL command line than if we do it via WordPress. Also, we know that later we will not leave more thrash uninstalling anything. Also, I will put other sentences that could be useful if you are doing debug tasks or programming on your site.
Remember, make a copy of your database before modifying anything, don’t say I didn’t warn you. I am not responsible if you break your site.
SQL sentences to clean WordPress
Clean the posts in the trash
There is not much to say, this SQL sentence will purge everything you have in the WordPress trash.
DELETE FROM `wp_posts` WHERE `post_status`="trash"
Delete SPAM comments
As the comment says, all comments that have been considered spam will disappear from your database.
DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam';
This sentence delete the pingbacks. If you don’t like them, you can finish them quickly with this SQL.
DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback';
Delete the orphan post-meta
Sometimes, when we delete a post, sometimes all its associated meta data is not deleted, as a result, the publication will not be found in ‘wp_posts’, but it will still have data in ‘wp_postmeta’. With this we take care of it.
DELETE m FROM `wp_postmeta` AS m LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID` WHERE p.`ID` IS NULL
Delete or Embed Cache
The oEmbed Cache is dedicated to cache embeds (like the typical embed of youtube, vimeo …). Cleaning it can free up a lot of space in our database.
DELETE FROM `wp_posts` WHERE `post_type`="oembed_cache"
Delete transient data
Transient data is data that is stored temporarily to gain processing speed later. It is like a cache. The problem is that it is filled and not cleaned and then leaves a lot of thrash in the installation.
DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient%';
Other useful sentences
Change the author of the articles
Sometimes, when we do a import of articles from a different source and we want to attribute all the posts to a single author, what I do is launch this sentence and… voilà, all the articles are converted to the ID of the author we specify.
UPDATE `wp_posts` SET `post_author` = '1' WHERE `post_type`='post' AND `post_status`='publish'
Delete posts from a post type
Do you want to delete all the products from your store? This is your SQL sentence. If you want to change the post type you want to delete, you just have to replace ‘product’ with your custom post type. This one is specifically for WooCommerce.
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'product'
Delete the categories of a taxonomy
This sentence will delete all the categories you have from a previously defined taxonomy. It is prepared to delete all WooCommerce categories, if you want to delete another different taxonomy, you just have to replace ‘product_cat’ with the taxonomy name you have to delete.
DELETE a,c FROM wp_terms AS a LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id WHERE c.taxonomy = 'product_cat' AND c.COUNT = 0;
And with these SQL sentences to clean WordPress we will have the database clean of things that take up space and that are no longer good for anything.
If it has been helpful, comment, share and give me 5 stars!