I am getting ‘Akismet has detected a problem. A server or network problem prevented Akismet from checking xxx comments. They have been temporarily held for moderation and will be automatically re-checked in xx mins.’ issue lately… This issue happens once in a while. And the longest last for 1 day. I had to delete all the spam comments manually by clicking ‘Check for Spam’. It is okay now as the time of writing this article. Hmm… Any idea what went wrong?
Anyway, I discovered another stuff ~ wp_commentmeta is the biggest size table in my WordPress database. What does wp_commentmeta store? After checking here and there. It has _wp_trash_meta_status data in meta_key. And lot of akismet_result, akismet_history, akismet_as_submitted, akismet_user_result and akismet_user entries too. wp_comments is only 8.5MB while wp_commentmeta is using more than 40MB database storage space. And it has more rows than wp_comments. Do these entries have any value at all?
After did some testings, I found out that wp_trash_meta data will be generated when you spam or trash a comment. And will be deleted after you delete the comment permanently. So the wp_commentmeta is clean. However, it will get bigger and bigger if you are using Akismet WordPress Plugin. Akismet will create 3 entries in wp_commentmeta on each comment. 5 entries if you spam the comment yourself manually.
I prefer a small and clean WordPress database. So it is time to clean it up. I used these entries to check and delete those unwanted entries.
Clean up wp_commentmeta entries that have no relation to wp_comments
select count(*) from wp_commentmeta where comment_ID not in
(select comment_ID from wp_comments)
delete from wp_commentmeta where comment_ID not in
(select comment_ID from wp_comments)
Clean up all Akismet wp_commentmeta entries
select count(*) from wp_commentmeta where meta_key like ‘%akismet%’
delete from wp_commentmeta where meta_key like ‘%akismet%’
Only performs these scripts in phpMyAdmin if you know what you are doing…
