Fernando Z. replied 3 years ago

1. Would we be able to get the same functionality with the Faster Woo Widgets plugin that we currently have with WooBeWoo + my custom code (priority_brand orderby and height/width filtering).

2. Agreed, the site feels faster on the front end when logged out. The shop page is very fast without any filters selected.

3. I added a CRON to set product_visibility to hidden (‘exclude-from-catalog’, ‘exclude-from-search’) for products of certain brands, so that’s one less addition to the query.

4. I don’t think we need the Super Speedy Search plugin, because the search page is pretty fast.

Dave H. Staff replied 3 years ago

Why not hide the products you don’t want to see through the back end? Anything permanent like this should not be SQL’d on the fly – update the products to hide them from the catalog.

Dave H. Staff replied 3 years ago

You say the shop is still slow, but since you removed that sorting, the main query dropped from 2.5 seconds to 0.2 seconds. Still not perfect, but it’s usable.

https://i.imgur.com/JkWEUHU.png

Fernando Z. replied 3 years ago

Hello, I am following up on our Discord chat here, since it is a very long reply.

In general, all urls are very slow, including in wp-admin. Even just the main WP dashboard page, or pages in wp-admin. I find those load times from Query Monitor to be inaccurate. In reality, wp-admin pages take about 8-10s to load for me.

I have deactivated that filter plugin (WooBeWoo) but the site is still slow, including wp-admin pages. Do we need the Faster Woo Widgets and Super Speedy Search plugins to speed up the entire site? I thought that would only improve speeds for the shop page and search page (i.e. the page with the “?s=” parameter).

I’m not sure what the dp-rewrite-republish check is from. Perhaps Yoast SEO but that plugin is disabled. The acf-disabled check is likely from the Advanced Custom Fields plugin.

As for the brand priority, this is custom code I wrote in my theme. This functionality allows us to order products by brand, based on the menu order in products > attributes > brands. I have set up a CRON to fill a custom meta field on each product (priority_brands) based on the menu order. My custom code orders by this meta value. Note that I deactivated this code (by commenting out the add_action calls) and the site is still slow.

add_action( ‘pre_get_posts’, ‘jwd_product_query_orderby’ );

function jwd_product_query_orderby($query) {
if ( !is_admin() && $query->is_main_query() && is_post_type_archive( ‘product’ ) ) {

$meta_query = (array) $query->get( ‘meta_query’ );

$meta_query[‘brand_priority_query’] = array(
‘key’ => ‘priority_brand’,
‘value’ => 0,
‘compare’ => ‘>’,
‘type’ => ‘NUMERIC’,
);

$query->set( ‘meta_query’, $meta_query );

// Order by brand
// Brand priority set in drag & drop order of the attribute
// Cron updates brand priority
$orderby = array(
‘brand_priority_query’ => ‘ASC’,
//’date’ => ‘DESC’,
);
$query->set( ‘orderby’, $orderby );

}
}

In addition to the code above, I have also added the following:

1. Hide products that have specific brands (based on a meta field value assigned to the brand)

is_main_query() && (is_post_type_archive(‘product’) || $query->is_search()) ) {

$brands = get_terms(‘pa_brand’);
if (!$brands) {
return false;
}

$brands_to_hide = array();

foreach ($brands as $brand) {
$ID = $brand->term_id;
$is_hide = get_field(“hide_brand”, “pa_brand_” .$ID);
if ($is_hide) {
$brands_to_hide[] = $ID;
}
}

if (!$brands_to_hide) {
return false;
}

$tax_query = (array) $query->get( ‘tax_query’ );

// Do not show from certain brands
$brand_exclusion_query = array(
‘taxonomy’ => ‘pa_brand’,
‘terms’ => $brands_to_hide,
‘operator’ => ‘NOT IN’,
);
$tax_query[] = $brand_exclusion_query;

$query->set( ‘tax_query’, $tax_query );

}
}

2. Filter products by width and height (I added another form to the filters on the shop page. It integrates with the WooBeWoo filters because both use $_GET parameters, and I simply added hidden inputs with values of WooBeWoo parameters to my form).

is_main_query() && is_post_type_archive( ‘product’ ) ) {

$meta_query = (array) $query->get( ‘meta_query’ );

// Width Query
$min_width = $_GET[‘width_min’] ?? false;
$max_width = $_GET[‘width_max’] ?? false;
if ($min_width !== false && $max_width !== false) {
$min = intval($min_width) === 0 ? 0.01 : $min_width;
$width_query = array(
‘key’ => ‘_width’,
‘value’ => array($min, intval($max_width)),
‘compare’ => ‘BETWEEN’,
‘type’ => ‘DECIMAL’,
);
$meta_query[] = $width_query;
}

// Height Query
$min_height = $_GET[‘height_min’] ?? false;
$max_height = $_GET[‘height_max’] ?? false;
if ($min_height !== false && $max_height !== false) {
$min = intval($min_height) === 0 ? 0.01 : $min_height;
$height_query = array(
‘key’ => ‘_height’,
‘value’ => array($min, intval($max_height)),
‘compare’ => ‘BETWEEN’,
‘type’ => ‘DECIMAL’,
);
$meta_query[] = $height_query;
}

$query->set( ‘meta_query’, $meta_query );

}
}

Here is the code I used to allow the DECIMAL type:

<?php
add_filter('get_meta_sql','cast_decimal_precision');
function cast_decimal_precision( $array ) {
$array['where'] = str_replace('DECIMAL','DECIMAL(10,3)',$array['where']);
return $array;
}

Dave H. Staff replied 3 years ago

For anyone on our Discord server, you can follow the rest of this thread here: https://discordapp.com/channels/837258064704438282/857584095618007070/898291387399798844

Dave H. Staff replied 3 years ago

It’s highly likely that Scalability Pro *has* optimised your main query, but Scalability Pro cannot do much with awful woocommerce filters where the SQL queries are not actually slow – where all the slowness happens in PHP. That’s why I created Faster Woo WIdgets to fix this other main issue for slowness in wordpress. To check your slow main query, delete all the indexes and deactivate scalability pro, then load your shop, open queries in query monitor, look for the MAIN query, check the time. Then reactivate scalability pro, add the indexes and check the main query time again. It’s always faster – that’s what it does. But you’re only as fast as your slowest bottleneck. I don’t advise refunding scalability pro, I advise adding faster woo widgets and super speedy search.

Fernando Z. replied 3 years ago

Hey, I tried Scalability Pro and I don’t think it’s helping. Actually it may be slowing the site down. I noticed the site is slow even when imports aren’t running, so I don’t think imports are causing the slow down. The database is very large, so that could be it. Could you please take a look in the back end? If there’s nothing Scalability Pro can do to speed it up, we’d like a refund please.

Dave H. Staff replied 3 years ago

Also note that your /shop/ page is probably very slow due to your filters. All other filters we have tested do not scale well. It’s why I coded up our Faster Woo Widgets plugin so be sure to check that out too.