Cleaning the terms in wordpress database, particularly removing unused or orphaned entries, involves a series of SQL queries to ensure data integrity across related tables. It is crucial to back up your database before running any of these queries.
The primary goal is often to remove terms that are no longer associated with any posts or content, indicated by a
count of 0 in wp_term_taxonomy.Here’s a set of SQL queries to clean
wp_term_taxonomy and related tables:- Delete terms with a count of 0: This removes the actual terms from
wp_termsthat are not linked to any content.
Code
DELETE FROM wp_terms WHERE term_id IN ( SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
- Delete corresponding entries in
wp_term_taxonomy: After removing the terms, remove the corresponding taxonomy entries.
Code
DELETE FROM wp_term_taxonomy WHERE term_id NOT IN ( SELECT term_id FROM wp_terms );
- Delete orphaned entries in
wp_term_relationships: This ensures that no relationships point to non-existentterm_taxonomy_ids.
Code
DELETE FROM wp_term_relationships WHERE term_taxonomy_id NOT IN ( SELECT term_taxonomy_id FROM wp_term_taxonomy );
Important Considerations:
-
Custom Taxonomies:These queries target general
term_idandcountvalues. If you have custom taxonomies and want to clean only specific ones (e.g.,post_tag), you can add aWHERE taxonomy = 'your_taxonomy_slug'clause to thewp_term_taxonomysubqueries. -
Orphaned Posts/Attachments:If you are cleaning up after deleting posts or attachments, you might also need to clean
wp_postmetaandwp_term_relationshipsentries whereobject_idno longer corresponds to an existingwp_posts.ID. -
Performance:For very large databases, consider running these queries during off-peak hours or in smaller batches if possible.
-
Testing:Always test these queries on a staging or development environment before applying them to a live production database.
Bài viết cùng chuyên mục, chủ đề