Bad performance on big e-commerce

  • mfcanovas

    (@mfcanovas)


    5 years ago

    First, thank you very much for this plugin that I’ve been using it for years.

    I’ve detected some performance problems when working with a big ecommerce.

    I’ve have more than 35.000 active products and about 1.000 discontinued products.

    The problem is exclude_discontinued_products function sets all discontinued product ids in post__not_in query arg, which is being translated in sql to wp_posts.ID NOT IN (…..).

    This approach does not work well in this context, as it turns a query of 50-80ms into a query of 6-8 seconds.

    I tried the same SQL query changing the WHERE clausule by AND NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE wp_posts.ID = pm.post_id AND meta_key ='_is_discontinued' AND meta_value = 'yes') and it has substantially improved the performance to 80ms (almost the same time as the original query).

    You may put this clausule using the posts_where filter

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author twoelevenjay

    (@twoelevenjay)

    @mfcanovas thank you very much for sharing this with me. Would you be willing to submit a pull request with your fix on Github?

    https://github.com/twoelevenjay/woocommerce-discontinued-products

    I think I solved it by changing function exclude_discontinued_products

    /**
 * Exclude discontinued products.
 * Add a the post__not_in argiment to the main query for products.
 *
 * @since 1.0.0
 * @param object $query Main WP Query.
 */
public function exclude_discontinued_products( $query ) {
	if ( ! is_admin() && ! $this->doing_dp_ids && $query->is_main_query() && ! is_single() && $ids_to_hide ) {
		$args = array(
			'meta_query' => array(
				array(
					'key' => '_is_discontinued',
					'compare' => 'NOT EXISTS'
				)
			));
		
	}
	return $query;
}
Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Bad performance on big e-commerce’ is closed to new replies.