wp database cleanup and optimization

wp_db_cleanup.sql

-- This list of sql commands cleans and optimized a wordpress database, good for speed and functionality in general
-- Remember in each query to replace table prefix with the one you are using

-- Remove post revisions
DELETE FROM wp_posts WHERE post_type = "revision";

-- Remove comments marked as spam
DELETE FROM wp_comments WHERE comment_approved = 'spam';
-- Remove unapproved comments
DELETE from wp_comments WHERE comment_approved = '0';
-- Remove pingbacks and trackbacks
DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

-- Remove unused tags and relationships
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

-- Remove transients
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');

-- Opitmize tables from within phpMyAdmin
-- https://wp-rocket.me/blog/make-wordpress-database-clean-whistle/