Spam comments are the worst. Even with Akismet active to prevent them from appearing on your site, they can still cause problems. That’s because the comment still gets stored and Akismet creates comment meta entries every time it does something. Over the course of time those unnecessary database entries can amount to hundreds of megabytes even on a reasonably small site. Multiply that by a couple hundred sites on a multisite network, and it can quickly start costing you real money in terms of hosting and causing serious problems when you back up your database.

Fortunately, MySQL can help you eliminate all this comment meta bloat with a few simple queries:

DELETE FROM wp_comments WHERE comment_approved = "spam";
DELETE FROM wp_commentmeta WHERE meta_key LIKE "akismet_%";

As always, make sure to back up your database before running any destructive queries on it.

Once you’ve deleted all of those Spam comments and Akismet meta entries, make sure to optimize the database tables.

OPTIMIZE TABLE wp_comments;
OPTIMIZE TABLE wp_commentmeta;

If you’re running a multisite network, you’ll have to run the delete queries and the optimizations for each site, or at least each site which is causing problems. To determine which tables are the most bloated, you can run a query like this:

SELECT table_name AS "Tables", 
    round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
    FROM information_schema.TABLES 
    WHERE table_schema = "YOUR_DATABASE_NAME" AND table_name LIKE "%comment%"
    ORDER BY (data_length + index_length) DESC;

This will list all comment and commentmeta tables sorted by size, from largest to smallest. Once you have a list of all tables you want to clean up, you can run the DELETE and OPTIMIZE queries for each table. Or, you could write a script to clear this generally useless data from your database at regular intervals.

Sometimes the wp_term_relationships table becomes bloated with many orphaned relationships. This happens particularly often if you’re using your site not as a blog but as some other type of content site where posts are deleted periodically. I recently worked on a site that had 18,000 term relationships for posts that no longer exist, and it was slowing the site down. In my case it was the way a real estate plugin from Placester IDX manages real estate listings, but you may find similar problems caused by other custom functionality.

Fortunately, MySQL can help you find these orphaned relationships with a pretty simple query:

SELECT * FROM wp_term_relationships
    LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
    WHERE wp_posts.ID is NULL;

It can even automatically delete the orphaned entries for you! Make sure you back up your database before running the following query:

DELETE wp_term_relationships FROM wp_term_relationships
    LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
    WHERE wp_posts.ID is NULL;

The number of rows deleted should match the number of rows returned in the SELECT query above.

Once you’ve deleted the unnecessary items, make sure to optimize the database table.

OPTIMIZE wp_term_relationships;