I've used database cleanup plugins to find orphaned post meta, comment meta, comment, etc. on a WordPress database in the past.
In case you're using InnoDB1 as your MySQL Engine2, which is the default from version 5.5, you might want FOREIGN KEYS to prevent this kind of orphanage.
Please do not believe the sounds out there that this degrades performance since it's only in effect when you delete, and with deletion, you do want a clean delete. It does not effect view/read performance at all.
--
-- Constraints for table `wp_commentmeta`
--
ALTER TABLE `wp_commentmeta`
ADD CONSTRAINT `comment_id` FOREIGN KEY (`comment_id`) REFERENCES `wp_comments` (`comment_ID`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_comments`
--
ALTER TABLE `wp_comments`
ADD CONSTRAINT `commet_post_id` FOREIGN KEY (`comment_post_ID`) REFERENCES `wp_posts` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_postmeta`
--
ALTER TABLE `wp_postmeta`
ADD CONSTRAINT `post_id` FOREIGN KEY (`post_id`) REFERENCES `wp_posts` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_posts`
--
ALTER TABLE `wp_posts`
ADD CONSTRAINT `author_id` FOREIGN KEY (`post_author`) REFERENCES `wp_users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_term_relationships`
--
ALTER TABLE `wp_term_relationships`
ADD CONSTRAINT `term_taxonomy_id` FOREIGN KEY (`term_taxonomy_id`) REFERENCES `wp_term_taxonomy` (`term_taxonomy_id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_term_taxonomy`
--
ALTER TABLE `wp_term_taxonomy`
ADD CONSTRAINT `term_id` FOREIGN KEY (`term_id`) REFERENCES `wp_terms` (`term_id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `wp_usermeta`
--
ALTER TABLE `wp_usermeta`
ADD CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `wp_users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;(Oh, by the way: this entry was written by Peter Molnar, and originally posted on petermolnar dot net.)
