We ALL know that WordPress just WOULDN’T exist without AWESOME SQL, yeah?
Okay, if you haven’t done it before… Some of us need to make sure we get content up on our blogs regardless of how busy we get. And sometimes, this can get out of hand. In a big way. “I have 58,126 posts… WTF??” Yes, the AutoBlog poster from the folks at WPMU Dev DOES do its job. I have to say, there probably should be a setting to avoid duplication – as of those 58,126 posts, there were only 2169 unique articles. We’ll just leave the blog unnamed for now. Regardless, even *I* didn’t want to see that many posts and surely not THAT much duplication. Surely could NOT be good for my rankings on that blog.
I looked for a plugin that might be the answer to this issue to no avail. I suspect that they are made for “I have a dozen or two duplicate posts and I don’t want to have to waste 20 minutes clicking and deleting” type issues. The plugin couldn’t even actually load UP these posts in 3 minutes of waiting. Here is the ultimate answer for this issue by Rene Reimann from WPKrauts.com… Rene – you saved me a WHOLE lot of problems. Thank you!
Here is a snip of his article – find the original here – Find and Delete Duplicate Posts in WordPress
To proceed further with our task of cleaning out duplicates in our database, we only need to change two minor things: First we change SELECT to DELETE to actually remove the entries. And second, we switch from selecting only a few columns, that we needed for a quick glance at the duplicates, to all columns: *. Take a look at our new SQL query.
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'
After you run this query from your database UI (that would mostly be phpMyAdmin or Adminer) or a console, you’re mostly done. Keep in mind, we recommend that you back up your database before running such a query.
Follow
