Clean WP database - WORDPRESS R.I.P

gistfile1.md

Consultas MYSQL para limpiar bases de datos de wordpress

WORDPRESS R.I.P

• Orphan rows
  • wp_posts -> wp_posts (parent/child)
  • wp_postmeta -> wp_posts
  • wp_term_taxonomy -> wp_terms
  • wp_term_relationships -> wp_term_taxonomy
  • wp_usermeta -> wp_users
  • wp_posts -> wp_users
• Other
  • wp_postmeta dupes
  • wp_postmeta dupes #2
  • wp_postmeta missing
  • wp_postmeta '_edit_lock' and '_edit_last' rows
  • wp_options 'transient' rows
  • wp_posts revisions

Líneas huerfanas

Por el cambio a MyISAM los foreing keys se perdieron y por ese motivo se pueden dar muchos huerfanos con el tiempo.

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

Checkear duplicados _wp_attached_file / _wp_attachment_metadata keys (solo debería haber uno por tipo de post).

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

Encontrar y comprobar la existencia de entradas meta_key identicas para un mismo post más de una vez.

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

Chequea por _wp_attached_file / _wp_attachment_metadata keys repetidos en 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 creados contra un post que editado por un usuario admin. Se pueden borrar sin problema. 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

Los valores transient se crear cada vez que wordpress o algún plugin genera una consulta compleja, es como un cache. Más informacion puede ser encontrada aquí 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

Cada vez que guardamos en Wordpress no crea una nueva revision y sus entradas wp_postmeta relacionados. Usa esta consulta para elimiar las revisiones mas antiguas de 15 días:

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))

Tambien querras correr wp_postmeta -> wp_posts huerfanos despues de borrar estos posts.