14 June 2018

Wordpress SQL cleanup database for duplicate and old data

If you want to cleanup your Wordpress database for some duplicate and old data there is SQL commands to do it via phpMyAdmin :

This SQL command will DELETE data for month condition :

DELETE FROM `wp_posts` WHERE `post_type` = "attachment" AND post_modified LIKE '2018-04-%'
DELETE FROM `wp_posts` WHERE `post_type` = "post" AND post_modified LIKE '2018-04-%'

This SQL command will SELECT data with duplicate title :

SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
 INNER JOIN (
   SELECT post_title, MIN( id ) AS min_id
   FROM wp_posts
   WHERE post_type = 'post'
   AND post_status = 'publish'
   GROUP BY post_title
   HAVING COUNT( * ) > 1
 ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'


This SQL command will DELETE data with duplicate title :


DELETE a.*
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'

This SQL command will DELETE Trash data:

DELETE wp_posts,wp_term_relationships,wp_postmeta,wp_term_taxonomy FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_term_taxonomy ON ( wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id ) WHERE wp_posts.post_status='trash'

This SQL command will DELETE orphaned postmeta data:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

This SQL command will DELETE orphaned relation data:

DELETE wp_term_relationships FROM wp_term_relationships
LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
WHERE wp_posts.ID is NULL;

After that do Optimize of all table throw phpmyadmin.

Off course, backup your data, before this commands.

No comments: