Quote from Dym1 on March 12, 2021, 23:03

More information: Sometimes in stock works, but takes ages to load if you have just a few filters selected. With just 1 filter it seems to be all fine (due to that inefficient query).

We also tried to debug that as a guest, it didn't work (because of the query again).

Quote from Dym1 on March 12, 2021, 23:30

More information from query monitor: On 5 filters, 1 category, 2 colors, 2 sizes these get called 124/125 times. The same queries are loaded over and over again. They are marked as slow + a lot of duplicates.

You can imagine what happens when more filters are selected.

Query	Count	Callers	Components	Potential Troublemakers
SELECT name FROM cuj_terms WHERE slug = '36'	125	apply_filters_ref_array() 125 calls	Core 1 call Plugin: woocommerce-products-filter 124 calls	WP->query_posts() 1 call WP_Query->__construct() 124 calls
SELECT name FROM cuj_terms WHERE slug = '38'	125	apply_filters_ref_array() 125 calls	Core 1 call Plugin: woocommerce-products-filter 124 calls	WP->query_posts() 1 call WP_Query->__construct() 124 calls
SELECT cuj_posts.* FROM cuj_posts INNER JOIN cuj_postmeta ON ( cuj_posts.ID = cuj_postmeta.post_id ) INNER JOIN cuj_postmeta AS mt1 ON ( cuj_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( cuj_postmeta.meta_key = '_stock_status' AND cuj_postmeta.meta_value = 'outofstock' ) AND ( ( ( mt1.meta_key = 'attribute_pa_size' AND mt1.meta_value = '36' ) OR ( mt1.meta_key = 'attribute_pa_size' AND mt1.meta_value = '38' ) ) ) ) AND cuj_posts.post_type = 'product_variation' AND (cuj_posts.post_status = 'publish' OR cuj_posts.post_status = 'acf-disabled' OR cuj_posts.post_status = 'private') GROUP BY cuj_posts.ID ORDER BY cuj_posts.post_date DESC	125	WP_Query->get_posts() 125 calls	Plugin: woocommerce-products-filter 125 calls	WP->query_posts() 1 call WP_QueryWOOFCounterIn->__construct() 124 calls

and details of the slow duplicate query (terms query is not slow but still duplicated a lot of times):

Query	Caller	Component	Rows	Time
SELECT cuj_posts.* FROM cuj_posts INNER JOIN cuj_postmeta ON ( cuj_posts.ID = cuj_postmeta.post_id ) INNER JOIN cuj_postmeta AS mt1 ON ( cuj_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( cuj_postmeta.meta_key = '_stock_status' AND cuj_postmeta.meta_value = 'outofstock' ) AND ( ( ( mt1.meta_key = 'attribute_pa_size' AND mt1.meta_value = '36' ) OR ( mt1.meta_key = 'attribute_pa_size' AND mt1.meta_value = '38' ) ) ) ) AND cuj_posts.post_type = 'product_variation' AND (cuj_posts.post_status = 'publish' OR cuj_posts.post_status = 'acf-disabled' OR cuj_posts.post_status = 'private') GROUP BY cuj_posts.ID ORDER BY cuj_posts.post_date DESC	- WP_Query->get_posts() wp-includes/class-wp-query.php:3034 WP_Query->query() wp-includes/class-wp-query.php:3465 WP_Query->__construct() wp-includes/class-wp-query.php:3576 WOOF_EXT_BY_INSTOCK->posts_where() wp-content/plugins/woocommerce-products-filter/ext/by_instock/index.php:263 apply_filters_ref_array('posts_where') wp-includes/plugin.php:255 WP_Query->get_posts() wp-includes/class-wp-query.php:2568 WP_Query->query() wp-includes/class-wp-query.php:3465 WP->query_posts() wp-includes/class-wp.php:629 WP->main() wp-includes/class-wp.php:752 wp() wp-includes/functions.php:1291	Plugin: woocommerce-products-filter	350	0.1778

 

Quote from Dym1 on March 12, 2021, 23:51

Just to clarify on that. Both are issues: slow and inefficient query that passes many ids in this set up to NOT IN condition and these duplicate calls.

Quote from Pablo Borysenco on March 15, 2021, 13:51

Hello

Unfortunately, this cannot be changed.

These requests are not duplicated, but are performed for each element in the filter. This is necessary for the counter to be displayed up to date for the current search query. There is no other way

You can try  to  use   a cache - https://c2n.me/4bj3Crq.png

Quote from Dym1 on March 15, 2021, 14:00

Hello,

we use caching, but just have a look at the site. That filter is unusable and crashes it with instock filter due to that inefficient query. So for example the filter makes 125 requests where each would take more than 0.5s (with that many ID exceptions) that totals into 60s+ queries. With just 4 filters + instock the queries take 28seconds!!!

That means the plugin is not optimized for this amount of products! Even when we tried that without dynamic recount, it still took ages or crashed.

It's not only about the duplicates, we can resolve that pretty quickly, but about the inefficient query which makes it to crash. Is there anything you can do about it? Rewriting where query would most probably help.

Thanks

Quote from Pablo Borysenco on March 16, 2021, 12:28

Hello

It's not only about the duplicates,  - as I described, there is no duplicate requests.

 the inefficient query - There is no magic. To obtain certain information, you need to make a request to the database.  It is possible to optimize queries using indexing, but this is implemented in caching

we use caching, but just have a look at the site. - Ok!   My  test - https://c2n.me/4bjUIP5  - When using cache, filtering works in about a second

Quote from Dym1 on March 16, 2021, 13:24

Hello,

I think you did the test wrong. I understand your post about duplicates, as the query needs to be checked for each filter. To debug the issue that I'm talking about, click 4 filters (category, color, material and any else) on the frontend and then click in stock. If loading takes ages reload the page you're going to get error 500. (You even have that test url in private section)

The same thing happens with caching on. Can you please have a look into it? I understand there must be queries done to the DB. But its about how the query is done in /ext/by_instock/index.php you do posts_where. You're using an exception to "NOT IN" there which starts to grow with amount of filters selected as it fills in all product ids to the NOT IN exception. And thats the problem. With this amount of variations/products, it's always going to be a bloated request. So for example on each request it tries to select more than a couple of hundreds of products for the exception, which is wrong.

In our case even with cache and with what's described above the "In Stock" filter is unusable leading to error 500.

I also understand that changing posts_where in stock filter would take time, but we're not the only shop where you are going to run into the issues in the future with this I guess, so its worth it rewriting it.

I posted this as the first thing of support topic (ids of products start to grow insanely with filters being selected). This only works well with just a few hundred variations available.

AND ( cuj_posts.ID NOT IN ( SELECT object_id FROM cuj_term_relationships WHERE term_taxonomy_id IN (7,9) ) AND cuj_term_relationships.term_taxonomy_id IN (168,169,170,178,185,204,205,207,208,210,215,223,231,233,238,239,255,273,276,282,287,293,297,298,302,304,308,317,321,334,340,351,371,401,402,455,494) AND tt1.term_taxonomy_id IN (19) ) AND ( ( cuj_postmeta.meta_key = '_stock_status' AND cuj_postmeta.meta_value NOT IN ('outofstock') ) AND ( cuj_postmeta.meta_key = '_stock_status' AND cuj_postmeta.meta_value NOT IN ('outofstock') ) ) AND cuj_posts.post_type = 'product' AND (cuj_posts.post_status = 'publish' OR cuj_posts.post_status = 'acf-disabled' OR cuj_posts.post_status = 'private') AND cuj_posts.ID NOT IN(15195,5168,5160,5163,1766,15202,15175,15291,15257,15281,14802,14676,14563,14613,14327,14266,14155,14096,14021,13824,13726,13687,13651,13633,13551,3401,13496,13479,13431,4922,12529,4760,12298,2967,2798,2708,4500,10943,10912,10892,4366,10570,10494,9804,9778,9708,9651,9613,9460,9417,9387,9399,2138,9134,8974,1991,8784,8624,8432,8228,8111,8071,7836,7817,7683,7667,7610,7453,1658,7233,7163,1415,6581,6328,2662,6227,6207,3759,3694,5453,5383,5210,5236,5078,4858,4748,3123,4671,4612,4565,2579,2524,4228,4251,4132,3968,3918,2674,3829,3817,3575,3026,2535,2323,2253,1896,1917,1887,1710,1506,995,960,916) 1

Quote from Pablo Borysenco on March 17, 2021, 12:55

Hello

OK!  New test  - https://c2n.me/4bkVffb

The  test  with  more  filters - https://c2n.me/4bkVpeP

I will pass  it to the developers

 

Quote from Dym1 on March 17, 2021, 17:12

Hello,

yes. But that's without dynamic recount option on. We need to hide filters accordingly. And the only issue with that was the slow/inefficient query.

Thanks for letting me know the status from developers.

Quote from Pablo Borysenco on March 18, 2021, 12:17

Hello

But that's without dynamic recount option on.  - No! the video clearly shows that empty terms are hidden. This is the job of the cache.