Wordpress选择计数查询优化

2 回答

• 0

  首先改进许多架构，如下所述：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

  SQL_CALC_FOUND_ROWS 主要是方便，因此您不必扫描表格两次 . （目前还不清楚你如何在这里使用它 . ）

  该查询看起来像"explode-implode"情况，其中 JOINs 导致更多行，然后 GROUP BY （或者在您的情况下为 DISTINCT ）缩小 .

  我不清楚为什么你在一个案例中有 LEFT JOIN ，而在另一个案件中 JOIN .

  可能每个_1179229都可以以这种格式添加到 WHERE 子句中：

  AND EXISTS (SELECT ... )
  

  那会让你摆脱 DISTINCT ，从而避免爆炸 - 内爆 . 那时，一个简单的_1179233就足够了 .

  回复于 2024-05-05T05:17:03+08:00
• 0

  由于每天都会多次调用该函数，因此我实际上并不需要实时更新我使用随机函数重新编码函数的产品数量 . 从统计上来说，它应该运行一次300次调用（编号42），这是完美的 . 真的设法大规模减少服务器负载 .

  它现在是一个WooCommerce核心黑客，但我可能会为此编写一个插件 .

  这是代码：

  function _wc_term_recount( $terms, $taxonomy, $callback = true, $terms_are_term_taxonomy_ids = true ) {
  $number = rand(1,300);
  if($number == 42) {
  global $wpdb;
  
  // Standard callback.
  if ( $callback ) {
      _update_post_term_count( $terms, $taxonomy );
  }
  
  $exclude_term_ids            = array();
  $product_visibility_term_ids = wc_get_product_visibility_term_ids();
  
  if ( $product_visibility_term_ids['exclude-from-catalog'] ) {
      $exclude_term_ids[] = $product_visibility_term_ids['exclude-from-catalog'];
  }
  
  if ( 'yes' === get_option( 'woocommerce_hide_out_of_stock_items' ) && $product_visibility_term_ids['outofstock'] ) {
      $exclude_term_ids[] = $product_visibility_term_ids['outofstock'];
  }
  
  $query = array(
      'fields' => "
          SELECT COUNT( DISTINCT ID ) FROM {$wpdb->posts} p
      ",
      'join'   => '',
      'where'  => "
          WHERE 1=1
          AND p.post_status = 'publish'
          AND p.post_type = 'product'
  
      ",
  );
  
  if ( count( $exclude_term_ids ) ) {
      $query['join']  .= " LEFT JOIN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id IN ( " . implode( ',', array_map( 'absint', $exclude_term_ids ) ) . " ) ) AS exclude_join ON exclude_join.object_id = p.ID";
      $query['where'] .= " AND exclude_join.object_id IS NULL";
  }
  
  // Pre-process term taxonomy ids.
  if ( ! $terms_are_term_taxonomy_ids ) {
      // We passed in an array of TERMS in format id=>parent.
      $terms = array_filter( (array) array_keys( $terms ) );
  } else {
      // If we have term taxonomy IDs we need to get the term ID.
      $term_taxonomy_ids = $terms;
      $terms             = array();
      foreach ( $term_taxonomy_ids as $term_taxonomy_id ) {
          $term    = get_term_by( 'term_taxonomy_id', $term_taxonomy_id, $taxonomy->name );
          $terms[] = $term->term_id;
      }
  }
  
  // Exit if we have no terms to count.
  if ( empty( $terms ) ) {
      return;
  }
  
  // Ancestors need counting.
  if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
      foreach ( $terms as $term_id ) {
          $terms = array_merge( $terms, get_ancestors( $term_id, $taxonomy->name ) );
      }
  }
  
  // Unique terms only.
  $terms = array_unique( $terms );
  
  // Count the terms.
  foreach ( $terms as $term_id ) {
      $terms_to_count = array( absint( $term_id ) );
  
      if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
          // We need to get the $term's hierarchy so we can count its children too
          if ( ( $children = get_term_children( $term_id, $taxonomy->name ) ) && ! is_wp_error( $children ) ) {
              $terms_to_count = array_unique( array_map( 'absint', array_merge( $terms_to_count, $children ) ) );
          }
      }
  
      // Generate term query
      $term_query          = $query;
      $term_query['join'] .= " INNER JOIN ( SELECT object_id FROM {$wpdb->term_relationships} INNER JOIN {$wpdb->term_taxonomy} using( term_taxonomy_id ) WHERE term_id IN ( " . implode( ',', array_map( 'absint', $terms_to_count ) ) . " ) ) AS include_join ON include_join.object_id = p.ID";
  
      // Get the count
      $count = $wpdb->get_var( implode( ' ', $term_query ) );
  
      // Update the count
      update_woocommerce_term_meta( $term_id, 'product_count_' . $taxonomy->name, absint( $count ) );
  }
  
  delete_transient( 'wc_term_counts' );
  }
  else {}
  

  }

  回复于 2024-05-05T05:17:03+08:00