Thread Starter
nm2net
(@nm2net)
btw, I dont know of this is actual for the current version of WP, but you should check it out : http://www.ambrosite.com/blog/clean-up-wordpress-revisions-using-a-mysql-multi-table-delete
I haven’t tested it recently but if you’re feeling brave you can try this query. It cleans wp_term_relationships by removing all “orphaned” records — that is, records that don’t have a corresponding object ID in the post or link tables. As with all delete queries, I recommend backing up your database before running it, just in case.
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);
wp_term_relationships is a complicated table.. it might effect some other part of the db that is why I didn’t plan to put it in.. you can try what @ambrosite said but do backup and let me know.. how that went..
Thread Starter
nm2net
(@nm2net)
They just didn’t made the database design well. InnoDB tables (except the most important one! – posts) and not a single relation.. however.
Thanks for your suggestion ambrosite. I ran the query with a SELECT just to check what it covers, and I see there are some records
WHERE tt.taxonomy = ‘category’. Would that be a problem, and should I include it into the where clause? /I am not very familiar with the WP details/
WHERE tt.taxonomy != 'link_category'
AND tt.taxonomy != 'category'
AND tr.object_id NOT IN (SELECT ID FROM wp_posts);
Thread Starter
nm2net
(@nm2net)
Anyways, it worked fine for me. Thanks ambrosite 😉
Additionally I used
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);
to clear the wp_postmeta table as well
Seems the author of this plugin has no website up now. Does that mean there is no support ?
Anyways, it worked fine for me. Thanks ambrosite 😉
Additionally I used
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts); to clear the wp_postmeta table as well
How many records did it clean out for you? I also have a large wp_term_relationships but only five we thrown out after that query
How many records were you expecting would be deleted? There is a many-to-many relationship between posts and taxonomy terms. If you have a lot of posts, you will have a lot of term relationships, especially if you have posts assigned to multiple categories. In general, you can expect your wp_term_relationships table to have at least as many records as your wp_posts table. In most cases, it will have more.
And don’t forget that post tags are taxonomy terms as well. If you tend to put a lot of tags on your posts, you could easily have a wp_term_relationships table with 5-10 times as many records as wp_posts.