Combine wp_terms and wp_term_taxonomy tables

[boonebgorges]

The two database tables wp_terms and wp_term_taxonomy are designed to facilitate "shared terms": single "terms" that are shared by multiple "term-taxonomy" pairs. In 4.1 we've begun the process of phasing out shared terms (#5809, #21950), and at some point we'll be able to guarantee that items in wp_terms are in one-to-one correspondence with those in wp_term_taxonomy (#30261). Once this has happened, we have no more need for two tables. So let's get rid of one of them.

The primary benefit of merging the two tables is that, over time, we can simplify the internals of much of our taxonomy API. No more JOINS will be necessary in get_terms(). There will be no need for WP_Tax_Query::transform_query(). And so forth. This will make the component easier to maintain, and will improve cachability and overall performance. A subsidiary benefit is that it makes the system a bit more sane for developers who are learning it for the first time.

The main concern in the process of merging the two tables is backward compatibility. As ​outlined by nacin in 2013, we have a pretty good plan for how we can maintain close to 100% backward compat. Two parts:

1. The table aliases $wpdb->terms and $wpdb->term_taxonomy should point to the same merged table, which will include all columns from both existng tables. This will provide support for anyone using these aliases to perform taxonomy queries.
2. Create a ​MySQL view that replicates the deprecated table. This will provide support for anyone querying the table with syntax like $wpdb->get_blog_prefix( $blog_id ) . 'terms'.

We can't do the migration for at least a version or two after #30261 is complete - we have to give time to all installations to get their shared terms cleared out before we can think about putting the tables together.

A viable patch for this ticket will include: a schema change to introduce the view, the migration logic to move the old columns into the new combined table, and the necessary changes to $wpdb.

[Funkatronic]

I nominate we name the combined table terms as apposed to the previously suggested term_taxonomy. Main reason: short and simple.

[nacin]

Replying to Funkatronic:

I nominate we name the combined table terms as apposed to the previously suggested term_taxonomy. Main reason: short and simple.

It was suggested for technical reasons, based on what IDs were in what table and what we were using as the master ID for a term by the time we did this. Certainly wp_terms is better for aesthetic reasons.