[wp-trac] [WordPress Trac] #62123: Improve performance of get_ancestors() with recursive common table expression.

[WordPress Trac]

#62123: Improve performance of get_ancestors() with recursive common table
expression.
-------------------------+-----------------------------
 Reporter:  Chouby       |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Taxonomy     |    Version:  3.1
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 `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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/62123>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform