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.
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)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)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)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)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)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)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)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
))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)
SELECT * 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)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')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\_%'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.
These are incredibly useful. Thank you!