[wp-trac] [WordPress Trac] #56598: Performance Optimization in file wp-includes\taxonomy.php

[WordPress Trac]

#56598: Performance Optimization in file wp-includes\taxonomy.php
-------------------------+---------------------------------------------
 Reporter:  dimitrisv    |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Taxonomy     |    Version:  6.0.2
 Severity:  critical     |   Keywords:  changes-requested needs-refresh
  Focuses:               |
-------------------------+---------------------------------------------
 {{{#!php
 <?php
 /**
          * Filters the post statuses for updating the term count.
          *
          * @since 5.7.0
          *
          * @param string[]    $post_statuses List of post statuses to
 include in the count. Default is 'publish'.
          * @param WP_Taxonomy $taxonomy      Current taxonomy object.
          */
         $post_statuses = esc_sql( apply_filters(
 'update_post_term_count_statuses', $post_statuses, $taxonomy ) );

         foreach ( (array) $terms as $term ) {
                 $count = 0;

                 // Attachments can be 'inherit' status, we need to base
 count off the parent's status if so.
                 if ( $check_attachments ) {
                         // phpcs:ignore
 WordPress.DB.PreparedSQLPlaceholders.QuotedDynamicPlaceholderGeneration
                         $count += (int) $wpdb->get_var( $wpdb->prepare(
 "SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts p1 WHERE
 p1.ID = $wpdb->term_relationships.object_id AND ( post_status IN ('" .
 implode( "', '", $post_statuses ) . "') OR ( post_status = 'inherit' AND
 post_parent > 0 AND ( SELECT post_status FROM $wpdb->posts WHERE ID =
 p1.post_parent ) IN ('" . implode( "', '", $post_statuses ) . "') ) ) AND
 post_type = 'attachment' AND term_taxonomy_id = %d", $term ) );
                 }

                 if ( $object_types ) {
                         // phpcs:ignore
 WordPress.DB.PreparedSQLPlaceholders.QuotedDynamicPlaceholderGeneration
                         $count += (int) $wpdb->get_var( $wpdb->prepare(
 "SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts WHERE
 $wpdb->posts.ID = $wpdb->term_relationships.object_id AND post_status IN
 ('" . implode( "', '", $post_statuses ) . "') AND post_type IN ('" .
 implode( "', '", $object_types ) . "') AND term_taxonomy_id = %d", $term )
 );
                 }

 }}}

 As I am running a rather large news-site (20Million rows in wp_postmeta,
 600k wp_posts, 500k wp_term_relationships, I noticed that the query:

 SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE wp_posts.ID =
 wp_term_relationships.object_id AND post_status IN (?) AND post_type IN
 (?) AND term_taxonomy_id = ?

 Can take a lot of time.

 Although the table is indexed Count(*) is not actually working. It needs
 to be made to Count(<pick your indexed fields of choice>) so that we can
 increase its speed 100fold (if not more).

 [[Image(<img src="https://db3pap004files.storage.live.com/y4mxBsrn4geeB-
 TrkH6Siuaw5ta6jzDtEDxGoZyKPu_pF-
 VQuZz4wjiCeMgQ1GCgrcEhq1-zLkusPu_rGciTGtH9WMD6c8n6xr1qx3vWyz6gY0
 -WrR22cfEnkCsWD1fitHeYhmImTxHc1TSYaT-OQxNKyUvK_XYw-
 PEiJqQdcX9HVWyfHZqHVUNxkvaEp22GKJ4?width=1389&height=693&cropmode=none"
 width="1389" height="693" />)]]

 (you will see that typical times are from 8 to 13 seconds)

 Thank you.

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