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.

This is some initial investigation into the feasibility of doing this. Thar be dragons.

[johnbillion]

Blocked by #62997.

I am very interested in being involved in this ticket. Please ping me for code review.

Cheers Jonny, will do. I'm going to try an alternative approach (converting wp_terms to a view and retaining the wp_term_taxonomy table) and then we can evaluate the pros and cons of both and go from there.

The approach of using a view has it's own problems. The biggest one to me is permissions. Do all database users have access to create a views? How will that work in Multisite?