Cleaning WP Database

  • I need to clean unlinked data in an existing a WP installation where some posts/pages/link etc where manually deleted through queries made directly on DB.
    I know DB structure and I can find unlinked records in comments, postdata etc, but I have some problems with terms and taxonomies.

    wp_term_relationships has an externale key ‘object_id’ that links both to wp_posts and wp_links, but without spec which… the same id can be in posts or links, so I can’t perform the right join/subquery to find non-existant posts/links.
    Do you have any suggestion about this?
    Thanks

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

  • Yes, the taxonomy tables are quite complex. Let me see if I can get this right. You may want to take it with some skepticism.

    1. The *_terms table has the raw term names and slugs.
    2. That table is linked to the *_term_taxonomy table with the ‘term_id’. The column name is the same in both tables. The *_term_taxonomy table sorts the terms into ‘types’– the ‘taxonomy’ column– like categories, links, custom taxonomies, etc. This table is also where you get your parent-child relationships for hierarchical types. It is an adjacency list so you have to make recursive joins on the table to get the whole tree, so this can be an expensive query.
    3. The *_term_taxonomy table is linked to the *_term_relationships table via the term_taxonomy_id column. The *_term_taxonomy table is where you get your association between the taxonomy and the posts/pages/whatever. The ‘object_id’ that you mentioned is the post or page ID, but I believe can be other things as well.

    So, you would need to start with the ‘object_id’, join against the *_term_taxonomy table for your taxonomy type– ‘category’, whatever–, and then against *_terms.

    Thread Starter taloweb

    (@taloweb)

    ok, you’re right, but I think that an information is missing or difficult to understand: how can I know where the object_id value of e arow in wp_term_relationship links to wp_posts or wp_links?

    Look at the table *_term_taxonomy table. It should be obvious. One of the taxonomies is ‘link_category’. If your theme or a plugin has added other taxonomies you will have to sort those out similarly.

    Thread Starter taloweb

    (@taloweb)

    ok, but this needs a “semantic” join, i.e. every type of link_category should be known

    I hope that wp_links removal in WP 3.5 will simplify such tasks…

    ok, but this needs a “semantic” join, i.e. every type of link_category should be known

    If you mean that you need to know what the items in the ‘taxonomies’ column mean, then yes you do.

    Thread Starter taloweb

    (@taloweb)

    yes,I meant that joining is dependent from column content.
    thanks

    It is that way on purpose. It allows plugin and themes to create custom taxonomies. If you use WordPress core functions, dealing with the taxonomies is easier, but that isn’t the question you asked.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Cleaning WP Database’ is closed to new replies.