WordPress database clean up

wp-cleanup.md

-- Delete all but posts and attachments
delete
from wp_posts
where post_type not ('post', 'attachment')

-- Select orphan post meta
select *
from wp_postmeta 
left join wp_posts on wp_posts.ID = wp_postmeta.post_id
where wp_posts.ID is null

-- Delete orphan post meta
delete wp_postmeta
from wp_postmeta 
left join wp_posts on wp_posts.ID = wp_postmeta.post_id
where wp_posts.ID is null

-- Delete all term taxonomies but categories and tags
delete
from wp_term_taxonomy
where taxonomy not in ('category', 'post_tag')

-- Select all terms without taxonomy
select *
from wp_terms 
left join wp_term_taxonomy on wp_term_taxonomy.term_id = wp_terms.term_id
where wp_term_taxonomy.term_id is null

-- Delete all terms without taxonomy
delete wp_terms
from wp_terms 
left join wp_term_taxonomy on wp_term_taxonomy.term_id = wp_terms.term_id
where wp_term_taxonomy.term_id is null

-- Select all terms relationships without taxonomy
select *
from wp_term_relationships 
left join wp_term_taxonomy on wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
where wp_term_taxonomy.term_taxonomy_id is null

-- Delete all term relationships without taxonomy
delete wp_term_relationships
from wp_term_relationships 
left join wp_term_taxonomy on wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
where wp_term_taxonomy.term_taxonomy_id is null

Common replaces

Replace media upload URLs in post content

update wp_posts
set post_content = replace(post_content, 'https://oldomain.com/wp-content/uploads', 'https://newdomain.com/wp-content/uploads')
where post_type = 'attachment'

Replace development GUIDs

Important: only replace development GUIDs

update wp_posts
set guid = replace(guid, 'http://domain.test', 'https://domain.com')