Woocommerce slow query with joins

Remco van Bers op 27/03/2017 17:12:52

Paar dingen die je kan doen:
1) een cache ervoor gooien
2) Nog meer geheugen en mysql tweaken dat hij dat allemaal ook gebruikt.
3) je AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' ) in je JOIN zetten. Dan filtert hij het er al namelijk eerder uit.
4) Naar de volgorde van je joins kijken.
5) Waarom zijn het left joins? Wellicht dat er 1 of 2 ook inner joins kunnen zijn waardoor de dataset kleiner wordt

Algemeen is voor mysql dat hoe sneller je de dataset kan verkleinen hoe sneller de query is.

function _wc_term_recount( $terms, $taxonomy, $callback = true, $terms_are_term_taxonomy_ids = true ) {
	global $wpdb;

	// Standard callback
	if ( $callback ) {
		_update_post_term_count( $terms, $taxonomy );
	}

	// Stock query
	if ( get_option( 'woocommerce_hide_out_of_stock_items' ) == 'yes' ) {
		$stock_join  = "LEFT JOIN {$wpdb->postmeta} AS meta_stock ON posts.ID = meta_stock.post_id";
		$stock_query = "
		AND meta_stock.meta_key = '_stock_status'
		AND meta_stock.meta_value = 'instock'
		";
	} else {
		$stock_query = $stock_join = '';
	}

	// Main query
	$count_query = "
		SELECT COUNT( DISTINCT posts.ID ) FROM {$wpdb->posts} as posts
		LEFT JOIN {$wpdb->postmeta} AS meta_visibility ON posts.ID = meta_visibility.post_id
		LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
		LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
		LEFT JOIN {$wpdb->terms} AS term USING( term_id )
		LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
		$stock_join
		WHERE 	post_status = 'publish'
		AND 	post_type 	= 'product'
		AND 	meta_visibility.meta_key = '_visibility'
		AND 	meta_visibility.meta_value IN ( 'visible', 'catalog' )
		$stock_query
	";

	// 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 = 'AND term_id IN ( ' . implode( ',', $terms_to_count ) . ' )';

		// Get the count
		$count = $wpdb->get_var( $count_query . $term_query );

		// Update the count
		update_woocommerce_term_meta( $term_id, 'product_count_' . $taxonomy->name, absint( $count ) );
	}

	delete_transient( 'wc_term_counts' );
}

Remco van Bers op 28/03/2017 10:09:18

Heb zelf geen ervaring met woocommerce en kijkend naar dat deze functie veel te veel doet ga ik hier ook niks mee kunnen zonder het product te onderzoeken.
Wellicht iemand hier die wel ervaring heeft met woocommerce?