Here’s a query for admins who are too lazy to check comments! Simply run this this query to bulk delete all unapproved comments. It’s a chore deleting spam comments in batches, let alone one-by-one, but you can remove them all in one go with this query: DELETE FROM wp_comments WHERE comment_approved = 'spam' Thanks to Joseph Michael Ambrosio for this query, which removes all revisions without unintended data loss and accidentally deleting link relationships. LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id) LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id ) LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id) If you want to delete all of the post revisions in your database in on hit, run this query: DELETE a,b,c Old post revisions quickly add up, especially if you have authors on your site who are constantly saving their work over many days. DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME' ĭon’t forget to replace META-KEY-NAME with the value you want to clear out. The wp_postmeta table also happens to be where your post data is stored, so when you run this query you’re hitting two birds with one stone. Let’s start with deleting leftover data from plugins you no longer have installed. It’s important to note that this article uses the default table prefix wp_, so make sure you change the prefixes in the SQL commands below match the ones used by your database.
The SQL section in phpMyAdmin is where you can enter SQL commands and then hit “Go” to run them. (I have a few databases to optimize since I used to have Multisite installed on my server!) In the image above, I’ve blurred the name of my database, but you get the idea – I’ve selected the first database for my site. Click on the one you want to clean up and then click the “SQL” tab. Once you’re in phpMyAdmin, you’ll see your website’s databases listed on the left. To access phpMyAdmin, login to cPanel for your site and click “phpMyAdmin” in the “Databases” section. There are several ways you can run SQL queries on your database, but if you have cPanel on your server the best and easiest option is phpMyAdmin. Optimizing Your WordPress Database with phpMyAdmin A plugin like BlogVault makes backing up your site easy. Whether you’re making small changes to your site or big ones, having a backup of your site ready to restore will give you peace of mind if something goes wrong. Note: Before making changes to your database, I highly recommend you backup your website first. In this post, we’ll look at some mySQL queries you can use to clean up your database in phpMyAdmin as well as some great plugins that make the task even easier. With WordPress, there are a few different ways you can tackle optimizing your database. This useless data leads to database bloat (I mean, do you really need to save the settings for themes you deleted years ago?), so cleaning up your database not only helps speed up your site so it loads faster, but can also clear up significant space in your database so it runs more efficiently.
So if you’ve been using WordPress for a while, chances are your database is cluttered and filled with tables you no longer need. Your WordPress database is like a filing cabinet for your website that stores all of your content, including posts, pages, comments, revisions and spam comments, as well as the settings for your themes and plugins.