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.