WordPress database clean up queries

Orphan rows
Other

Orphan rows

Since WordPress uses MyISAM for it's storage engine, we don't get foreign keys relationships as offered by InnoDB/etc. - thus orphan rows can show themselves over time.

wp_posts -> wp_posts (parent/child)

	SELECT * FROM wp_posts
	LEFT JOIN wp_posts child ON (wp_posts.post_parent = child.ID)
	WHERE (wp_posts.post_parent <> 0) AND (child.ID IS NULL)

	DELETE wp_posts FROM wp_posts
	LEFT JOIN wp_posts child ON (wp_posts.post_parent = child.ID)
	WHERE (wp_posts.post_parent <> 0) AND (child.ID IS NULL)

wp_postmeta -> wp_posts

	SELECT * FROM wp_postmeta
	LEFT JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID)
	WHERE (wp_posts.ID IS NULL)

	DELETE wp_postmeta FROM wp_postmeta
	LEFT JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID)
	WHERE (wp_posts.ID IS NULL)

wp_term_taxonomy -> wp_terms

	SELECT * FROM wp_term_taxonomy
	LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
	WHERE (wp_terms.term_id IS NULL)

	DELETE wp_term_taxonomy FROM wp_term_taxonomy
	LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
	WHERE (wp_terms.term_id IS NULL)

wp_term_relationships -> wp_term_taxonomy

	SELECT * FROM wp_term_relationships
	LEFT JOIN wp_term_taxonomy
		ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
	WHERE (wp_term_taxonomy.term_taxonomy_id IS NULL)

	DELETE wp_term_relationships FROM wp_term_relationships
	LEFT JOIN wp_term_taxonomy
		ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
	WHERE (wp_term_taxonomy.term_taxonomy_id IS NULL)

wp_usermeta -> wp_users

	SELECT * FROM wp_usermeta
	LEFT JOIN wp_users ON (wp_usermeta.user_id = wp_users.ID)
	WHERE (wp_users.ID IS NULL)

	DELETE wp_usermeta FROM wp_usermeta
	LEFT JOIN wp_users ON (wp_usermeta.user_id = wp_users.ID)
	WHERE (wp_users.ID IS NULL)

wp_posts -> wp_users

	SELECT * FROM wp_posts
	LEFT JOIN wp_users ON (wp_posts.post_author = wp_users.ID)
	WHERE (wp_users.ID IS NULL)

	DELETE wp_posts FROM wp_posts
	LEFT JOIN wp_users ON (wp_posts.post_author = wp_users.ID)
	WHERE (wp_users.ID IS NULL)

Other

wp_postmeta dupes

Checking for dupe _wp_attached_file / _wp_attachment_metadata keys (should only ever be one each per attachment post type).

	SELECT post_id,meta_key,meta_value
	FROM wp_postmeta
	WHERE (meta_key IN('_wp_attached_file','_wp_attachment_metadata'))
	GROUP BY post_id,meta_key
	HAVING (COUNT(post_id) > 1)

wp_postmeta dupes #2

Where an identical meta_key exists for the same post more than once.

	SELECT *,COUNT(*) AS keycount
	FROM wp_postmeta
	GROUP BY post_id,meta_key
	HAVING (COUNT(*) > 1)

	DELETE FROM wp_postmeta
	WHERE (meta_id IN (
		SELECT * FROM (
			SELECT meta_id
			FROM wp_postmeta tmp
			GROUP BY post_id,meta_key
			HAVING (COUNT(*) > 1)
		) AS tmp
	))

wp_postmeta missing

Checking for missing _wp_attached_file / _wp_attachment_metadata keys on wp_posts.post_type = 'attachment' rows.

	SELECT * FROM wp_posts
	LEFT JOIN wp_postmeta ON (
		(wp_posts.ID = wp_postmeta.post_id) AND
		(wp_postmeta.meta_key = '_wp_attached_file')
	)
	WHERE (wp_posts.post_type = 'attachment') AND (wp_postmeta.meta_id IS NULL)

	DELETE FROM wp_posts
	LEFT JOIN wp_postmeta ON (
		(wp_posts.ID = wp_postmeta.post_id) AND
		(wp_postmeta.meta_key = '_wp_attachment_metadata')
	)
	WHERE (wp_posts.post_type = 'attachment') AND (wp_postmeta.meta_id IS NULL)

wp_postmeta '_edit_lock' and '_edit_last' rows

Rows created against a post when edited by a WordPress admin user. They can be safely removed.

	SELECT * FROM wp_postmeta
	WHERE meta_key IN ('_edit_lock','_edit_last')

	DELETE FROM wp_postmeta
	WHERE meta_key IN ('_edit_lock','_edit_last')

wp_options 'transient' rows

A transient value is one stored by WordPress and/or a plugin generated from a complex query - basically a cache. More information can be found in this answer on Stack Overflow.

	SELECT * FROM wp_options
	WHERE option_name LIKE '%\_transient\_%'

	DELETE FROM wp_options
	WHERE option_name LIKE '%\_transient\_%'

wp_posts revisions

Every save of a WordPress post will create a new revision (and related wp_postmeta rows). To clear out all revisions older than 15 days:

	SELECT * FROM wp_posts
	WHERE
		(post_type = 'revision') AND
		(post_modified_gmt < DATE_SUB(NOW(),INTERVAL 15 DAY))
	ORDER BY post_modified_gmt DESC

	DELETE FROM wp_posts
	WHERE
		(post_type = 'revision') AND
		(post_modified_gmt < DATE_SUB(NOW(),INTERVAL 15 DAY))

You may need to run the wp_postmeta -> wp_posts orphans query after cleaning up revisions.

IlanVivanco/WP DB Clean up.md