Improve performance of get_ancestors() with recursive common table expression.

[Chouby]

get_ancestors() currently makes one DB query - through get_term() calls - per term in the hierarchy. This could be improved to have a unique query to get all ancestors using recursive common table expressions. The same for get_post_ancestors(). See ​https://dev.mysql.com/doc/refman/8.0/en/with.html

Here is a simple example of such a query to get all the parents of the term with term_id 6.

with recursive cte (term_id, parent) as (
  select     term_id,
             parent
  from       wp_term_taxonomy
  where      term_id = 6
  union all
  select     tt.term_id,
             tt.parent
  from       wp_term_taxonomy tt
  inner join cte
          on cte.parent = tt.term_id
)
select * from cte;

Caveat: this kind of queries requires MySQL 8 while the minimum version supported is still 5.7.

[pbearne]

@Chouby do you want to create some to do this?
it would need to check MySQL version first.

[Chouby]

After doing some tests, the proposal proves to be counterproductive in practical use cases.

For example, in the terms list table, terms are added to the cache prior to the call to get_ancestors(). So in this use case, probably the most frequent, the calls to get_term() in the function don't query the DB.

On the opposite, with this proposal, each call to get_ancestors() would result in one extra DB query.

[swissspidy]

Removing milestone from closed ticket.