How to Clean up Comment Meta in WordPress Database


If you have a wordpress blog for sometimes, you may notice that, wordpress database keeps growing. The table that really growing is “wp_ commentmeta”. I check in one of my oldest blogs. The tables size are as follows,

wp_comment ~ 0.5MB
wp_commentmeta ~ 8MB
wp_posts ~ 4MB
wp_postsmeta ~ 0.6MB

“wp_commentmeta” contains more data than all my posts. Is it a useful data or just a junk?

After a quick check on the table, it contains mostly “akismet_result”, “akismet_history” & “akismet_as_submitted” meta key. Search on the internet regarding this, reveal that the entries created by akismet plugins in relation to spam comments. The entries will not get deleted even after all spam comments deleted permanently.

Due to this I believe it should be OK to purge it. However, remember to backup your database prior to removing these entries.

Once done with the database backup log in to phpMyAdmin, select the right database & click on “SQL” menu on the top.

There are two (2) steps to fully remove the junk.

1.) Clean up “wp_commentmeta” entries which have no relation to wp_comments

Use the following command to look at “junk” entries. It also shows the number of “junk” entries.

SELECT * FROM wp_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)

Once satisfied, execute the following command to remove the “junk” entries.

DELETE FROM wp_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)

After optimizing the database, the table size drop to ~5MB.

2.) Clean up Akismet related metadata in wp_commentmeta table.

Use the following command to look at “junk” entries. It also shows number of “junk” entries.

SELECT * FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

Once satisfied, execute the following command to remove the “junk” entries.

DELETE FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

After perform database optimization, table size drop to 0MB. That is a whopping 8MB of junk.



4 comments… add one
  • Thanks for the simple and fabulous tips! Just deleted 800MB of junk from my database after 4 years of accumulated akismet entries!

    Reply
  • thanks, dropped 5mb off the table 🙂

    Reply
  • Many thanks for the code, I’m surprised there isn’t a plugin for this – maybe you should create one! 😉 I knocked 580Mb off a client’s Wordpress DB and deleted 318,000 rows of unneeded spam comments!

    Reply
  • akismet is great Plugin for stopping spam comments to our websites, but in most cases it’s not able to protect us from stammers. I am transfer my blog hexojs to wordpress and activate akismet. it’s show 100-200 spam comment in my dashboard every day but after using “zero spam” Plugin spam comments down to zero. you should try it for your blog.

    Reply

Leave a Comment