10 useful SQL Queries To Clean Up Your WordPress Database

While working on WP is a great experience, however, all your work piles up and in no time, it can become a hefty platform. To save myself from this dreaded situation I use certain SQL queries which keep my WP squeaky clean! Those are:

• Delete all the revisions: Using DELETE FROM `wp_posts` WHERE `post_type`="revision" query you can clear all your previous work revisions from the database.
• Remove those trash: All of your work that you have removed goes to the trash section in the database. It stays there forever. To get rid of them completely use DELETE FROM `wp_posts` WHERE `post_status`="trash". This will save you a lot of time if you have many posts.
• Why keep those spam comments? There is a funny meme that states “The internet is dark and full of spam”. It couldn’t have been more apt for spam mails and WordPress. 

IF you want to get rid of the spam from your WP, just type DELETE FROM wp_comments WHERE comment_approved = 'spam'. 

• Let the transients go: Yes, for Wp users transients are super useful, as they store those kind of data that takes a long while to be found. But transients hold a lot of your database, especially when you are not managing it properly.

So use DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%') to have some extra space in your database.

• Ping the pingbacks bye: DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback' to delete all the pingbacks from the comments section.
• Clean the metadata: Type the below query:

DELETE m FROM `wp_postmeta` AS m

LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID

WHERE p.`ID` IS NULL. 

This little query will delete all the metadata that aren’t connected with any post.

• From keywords to plugins, say bye to it all: DELETE FROM `wp_postmeta` WHERE `meta_key` LIKE '%aktt%'. Using this, clean all the metadata, and the keywords. It is also very handy to remove a common plugin meta, using that particular keyword.
• Sayonara to shortcodes: If you are like me and have tested and tried many plugins, then this query is just for you. All you need to do is type UPDATE wp_post SET post_content = replace(post_content, '[your-shortcode]', '' ) and you are devoid of all the shortcodes that you didn’t even know existed!
• For the admins! If you are one of those blog admins who are tired of bulk, unapproved admins, then use DELETE from wp_comments WHERE comment_approved = '0'. Within seconds all the unwanted, not approved comments will be gone, just like Harry Potter would have done!
• Update that maid ID: Inside the WP database tables all the data are stored. So, you can use a simple SQL query to update your admin mail ID. Write:

UPDATE `wp_users` SET `user_email` = "new_email_address" WHERE `wp_users`.`user_login` = "admin". This will help you to update any mail Id within the wp_users` table.

While writing this post did I realise, my own WP was cluttered and this post helped me to clear a lot of it. I am not bragging, but saying these queries are gems in themselves. For a developer it is important his/ her platform is crisp, impeccable so that the codes are crisp and spotless themselves.

#WordPress #WordPressDatabase #SQLQueries #WordPressTipsandTricks

Try them yourselves and share your experiences!

Lovingly,

Rina