Opened 10 years ago
Last modified 8 weeks ago
#30262 accepted enhancement
Combine wp_terms and wp_term_taxonomy tables
Reported by: |
|
Owned by: |
|
---|---|---|---|
Milestone: | Future Release | Priority: | low |
Severity: | normal | Version: | |
Component: | Taxonomy | Keywords: | has-patch has-unit-tests |
Focuses: | performance | Cc: |
Description
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:
- 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. - 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
.
Change History (11)
#2
in reply to:
↑ 1
@
10 years ago
Replying to Funkatronic:
I nominate we name the combined table
terms
as apposed to the previously suggestedterm_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 ticket was mentioned in Slack in #core by boone. View the logs.
10 years ago
This ticket was mentioned in Slack in #core by boone. View the logs.
9 years ago
This ticket was mentioned in Slack in #core by gilliginsisland. View the logs.
9 years ago
This ticket was mentioned in PR #8354 on WordPress/wordpress-develop by @johnbillion.
2 months ago
#6
- Keywords has-patch has-unit-tests added
This is some initial investigation into the feasibility of doing this. Thar be dragons.
#7
@
8 weeks ago
- Focuses performance added
- Milestone set to Future Release
- Owner set to johnbillion
- Status changed from new to accepted
Blocked by #62997.
This ticket was mentioned in Slack in #core-performance by johnbillion. View the logs.
8 weeks ago
@spacedmonkey commented on PR #8354:
8 weeks ago
#9
I am very interested in being involved in this ticket. Please ping me for code review.
@johnbillion commented on PR #8354:
8 weeks ago
#10
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.
@spacedmonkey commented on PR #8354:
8 weeks ago
#11
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?
I nominate we name the combined table
terms
as apposed to the previously suggestedterm_taxonomy
. Main reason: short and simple.