How to Clean Orphan Records from WordPress Comments Meta Data Table

While doing routine wordpress database backup i surprised to see size of wp_commentmeta table which was 55MB. On the other side main comments table size was only few KBs. I checked both table rows and found that comments meta data table contain thousands of rows for comments that actually not exists in wp_comments table. Those comments either deleted manually or marked as spam and then deleted after 15 days but meta data records not deleted in this process. This was possibly a bug in older akismat plugin that seems have been fixed in recent version but still we need to delete orphan records to reduce database size.

1. Login to your hosting control panel and access PhpMyAdmin web interface. If your are using cpanel it is pretty easy.
2. Backpup your database first before doing anything.
3. Open SQL Query windows and run below command. It will show all orphan comments meta data. Replace table name prefix (wp_) in query with your actual table name prefix you used during installation. You can also check table prefix in phpmyadmin.

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

4. Verify records shown in above query results. If you are sure run below command in SQL Query window. Replace table prefix with actual table prefix.

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

5. All Done.