[wp-trac] [WordPress Trac] #54346: Slow SQL queries fetching posts from specific categories

[WordPress Trac]

#54346: Slow SQL queries fetching posts from specific categories
-------------------------+------------------------------------
 Reporter:  Krstarica    |       Owner:  (none)
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Awaiting Review
Component:  Taxonomy     |     Version:  5.8.1
 Severity:  normal       |  Resolution:
 Keywords:               |     Focuses:  rest-api, performance
-------------------------+------------------------------------

Comment (by Krstarica):

 Here is the solution.

 In wp-includes/class-wp-tax-query.php replace:
 {{{
 $alias = $this->find_compatible_table_alias( $clause, $parent_query );
 if ( false === $alias ) {
         $i     = count( $this->table_aliases );
         $alias = $i ? 'tt' . $i : $wpdb->term_relationships;

         // Store the alias as part of a flat array to build future
 iterators.
         $this->table_aliases[] = $alias;

         // Store the alias with this clause, so later siblings can use it.
         $clause['alias'] = $alias;

         $join .= " LEFT JOIN $wpdb->term_relationships";
         $join .= $i ? " AS $alias" : '';
         $join .= " ON ($this->primary_table.$this->primary_id_column =
 $alias.object_id)";
 }

 $where = "$alias.term_taxonomy_id $operator ($terms)";
 }}}
 with:
 {{{
 $where = "$this->primary_table.$this->primary_id_column IN (
         SELECT object_id
         FROM $wpdb->term_relationships
         WHERE term_taxonomy_id IN ($terms)
 )";
 }}}

 Such query takes 1.3058 seconds. That saves 0.5 seconds.


 To further speed it up, in wp-includes/class-wp-query.php replace:
 {{{
 if ( ! empty( $this->tax_query->queries ) || ! empty(
 $this->meta_query->queries ) ) {
         $groupby = "{$wpdb->posts}.ID";
 }
 }}}
 with:
 {{{
 if ( ! empty( $this->meta_query->queries ) ) {
         $groupby = "{$wpdb->posts}.ID";
 }
 }}}

 Such query takes 0.1804 seconds.

 Important: since we removed "GROUP BY wp_posts.ID" for tax queries, we
 need to make sure that all other cases in WP_Tax_Query->get_sql_for_clause
 use subqueries, too.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/54346#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform