Currently, the wb_terms table has indexes on individual fields:
CREATE INDEX /*i*/wb_terms_entity_id ON /*_*/wb_terms (term_entity_id);
CREATE INDEX /*i*/wb_terms_entity_type ON /*_*/wb_terms (term_entity_type);
CREATE INDEX /*i*/wb_terms_language ON /*_*/wb_terms (term_language);
CREATE INDEX /*i*/wb_terms_type ON /*_*/wb_terms (term_type);
CREATE INDEX /*i*/wb_terms_text ON /*_*/wb_terms (term_text);
CREATE INDEX /*i*/wb_terms_search_key ON /*_*/wb_terms (term_search_key);
Since only a single index will be used for any given query, this is not helpful, and results in inefficient searches. We really need a composite key for each kind of query. As far as I remember, the kinds of queries we do are:
- by term_language, term_term, and term_entity_type
- by term_language, term_term, and term_type
"term" here may be an exact match, or a soft match - so we need indexes covering either. The term type has very low cardinality (3 or 4, iirc), so it can probably be admitted.
From this, I gather we need the following two composite indexes:
- for exact matches: ( term_language, term_term )
- for soft matches: ( term_language, term_search_key )
We also need to access terms by entity ID (especially for deleting and updating, so we also want an index on
- ( term_entity_type, term_entity_id )
And of course, row_id will remain a primary key.
Version: unspecified
Severity: major
Whiteboard: backlog, termsearch