How to Remove "Orphaned" term_relationships

  • robscott

    (@robscott)


    14 years, 3 months ago

    Hi,

    I discovered a heap of attempted posts in our website from October 2011 forwards, from spam user accounts.

    I used SQL to remove most of this, for the sake of speed (there were 60,000+ posts awaiting “moderation” from “contributor” accounts!), and also ran a query I made to remove unused tags.

    Everything is down to the most streamlined it can be EXCEPT the term_relationships table, which still references a lot of the posts and tags I have now deleted.

    I know that this table simply joins tags/cats to posts, so I also know there will be a good SQL query to delete those which relate to either cats or tags which are not present, or posts which don’t exist.

    Just wondering if anyone has the SQL query I’d need for this already – the couple I came up with aren’t working, and my mind is now foggy after working out how to do the rest.

    I’ve collected a lot of good SQL snippets over the years, but none for this issue!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter robscott

    (@robscott)

    14 years, 3 months ago

    Incidentally, if anyone is interested, I can post up the other SQL queries I used / created to help them get over this issue.

    It cropped up because we use a front-end form to accept posts, so rarely fish around in the WordPress “Posts” menu except on routine maintenance. Our server is pretty hefty, so didn’t notice the 60,000+ “draft” posts sitting there…

    But this is something that seems to have started automatically in about October 2011 – when, presumably, somebody worked out how to do this automatically to list to blogs taking guest posts.

    We reverted user type to “subscriber” and deleted all rogue accounts also.

    Thread Starter robscott

    (@robscott)

    14 years, 3 months ago

    Ok guys, as always, no answer, and I answer my own question before getting there, below is the SQL query.

    Remember backup backup backup before doing this – you might break things, and don’t blame me if you do!

    DELETE tr FROM wp_term_relationships tr
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE tt.taxonomy != 'link_category'
AND tr.object_id NOT IN (SELECT ID FROM wp_posts);
    Thread Starter robscott

    (@robscott)

    14 years, 3 months ago

    Incidentally, running the query HALVED the size of my total database, so was worth looking into 🙂

    Hope this helps somebody counter a lot of spam.

    We’ve also turned off self registration – we don’t really need it, as we know the people who post for us.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘How to Remove "Orphaned" term_relationships’ is closed to new replies.