Make WordPress Core

Opened 7 weeks ago

Last modified 7 days ago

#62123 new enhancement

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

Reported by: chouby's profile Chouby Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 3.1
Component: Taxonomy Keywords:
Focuses: performance Cc:

Description

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.

Change History (2)

This ticket was mentioned in Slack in #core-performance by mukeshpanchal27. View the logs.


7 days ago

#2 @pbearne
7 days ago

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

Note: See TracTickets for help on using tickets.