What Happened?
My blog has been up and running in some form since 2007 and over years, I’ve had times where I had time to write, and times where I didn’t.
Over the last weeks, I’ve been working to restore all of the functionality, do updates and get it back on track. I find writing down thoughts really helps me to think certain topics through.
Anyways, one of the problems at the top of my list is the number of trash/spam comments I’ve received over the last few years. The count was over 4.5k and WordPress doesn’t make it super easy to delete in such volumes.
What to do?
I could take the easy route and install a plugin with bulk removal, but I like technical things, so why not find a way to delete it directly from the database? 
After all, my hosting provider enables c-panel and phpMyAdmin, so why not give it a go?
Note, if you’re unfamiliar with c-panel and its capabilities, I highly recommend doing a youtube search for your provider’s name and c-panel administration. There are lots of great videos explaining it’s why, what and how.
Doing a Back Up
After logging go to the phpMyAdmin, click the “export” tab and take a backup of your database, this is good practice; forgo this step at your own risk!
Then click “GO” (bottom right). A .sql file will download to your machine. Keep this safe if you need to recovery in the future.
Running SQL commands
Navigate to the “SQL” tab to first test your statement and then execute.
First, to remove the unwanted pending comments & SPAM use the following statements and click “Simulate Query”. If you’re happy with the proposed deletions, click “GO”
DELETE FROM wp_comments WHERE comment_approved = "0" or comment_approved = "SPAM"Congratulations, you’ve deleted the unwanted comments and spam.
Removed orphaned Meta Data
The wp_commentmeta table is used to store additional information about comments made on a WordPress site. Each row in the table represents a piece of comment metadata associated with a particular comment.
Comment metadata is often used to store additional information about a comment, such as the IP address of the commenter, the user agent string of the browser they used, and so on. It can also store custom information that a WordPress plugin or theme has added.
Once the comments are gone, removing the orphaned metadata about these now-deleted comments might help to keep the site fast and removes potentially stale data. Follow the process above to remove the metadata.
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)I hope this helps! As always, reach you if you have any questions or comments.
