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)

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)

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 will also want to run the wp_postmeta -> wp_posts orphans query after removing these posts.

Empty Cron

UPDATE `wp_options` SET `option_value` = '' WHERE `option_name` ='cron';

Autoloaded Options

List rows that are set autoload = 'yes'. Sorting them by field length.

SELECT * FROM `wp_options` ORDER BY LENGTH(`option_value`) DESC;

Get autoloaded options size in megabytes.

CREATE TABLE db_name.test_table SELECT * FROM `wp_options`;

SELECT round((data_length / 1024 / 1024), 2) "Size in MB" 
      FROM information_schema.TABLES 
    WHERE table_schema = "db_name" AND table_name = "test_table";

Don't forget to delete table 'test_table' after that.

Plugin's related

SELECT * FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

DELETE FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

Dimasmagadan/README.md