Severe performance issue: get_filtered_price() query scans ~1M rows, takes 8+ seconds per filter click
The support doesn work on Saturdays and Sundays, so some Friday requests can be answered on Monday. If you have problems with registration ask help on contact us page pleaseIf you not got email within 24~36 business hours, firstly check your spam box, and if no any email from the support there - back to the forum and read answer here. DO NOT ANSWER ON EMAILS [noreply@pluginus.net] FROM THE FORUM!! Emails are just for your info, all answers should be published only here.
The support doesn work on Saturdays and Sundays, so some Friday requests can be answered on Monday.
Hi,
We're running HUSKY - Products Filter Professional (v3.3.9) on a
WooCommerce store with approximately 25,000 products.
Our hosting provider's engineering team investigated a severe
slowdown when customers click any filter, and identified the
following query — generated by get_filtered_price() in
classes/helper.php — as the root cause:
SELECT min(FLOOR(price_meta.meta_value + 0.0)) as min_price,
max(CEILING(price_meta.meta_value + 0.0)) as max_price
FROM wp_posts
LEFT JOIN wp_postmeta price_meta ON wp_posts.ID = price_meta.post_id
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
WHERE wp_posts.post_type = 'product'
AND wp_posts.post_status = 'publish'
AND price_meta.meta_key IN ('_price')
AND price_meta.meta_value > ''
AND ( wp_term_relationships.term_taxonomy_id IN (159,161,162,...)
AND tt1.term_taxonomy_id IN (159,161,162,...) )
Our host's findings:
- Execution time: 8.39 seconds
- Rows examined: 949,038 (despite only ~25,000 products — likely due
to the double JOIN on wp_term_relationships causing row
duplication)
- A repeated test with a different category came in at 2.3–2.5
seconds
- Because the query wraps meta_value in FLOOR()/CEILING(), no index
can help with that part of the calculation
We already have"Cache dynamic recount","Cache terms", and
"Optimize price filter" all enabled, but since this query includes
category-specific WHERE conditions, the transient cache doesn't
cover every filter combination — so most filter clicks still hit
this slow path on first use.
Questions:
1. Is there a known fix or optimized version of get_filtered_price()
that avoids the FLOOR/CEILING wrapping and/or the double JOIN?
2. Is there a way to pre-calculate and cache min/max price per
category (rather than computing it live on every filter click)?
3. Any other recommended approach given our catalog size (~25k
products)?
Happy to provide more details (query plan, site URL, etc.) if
helpful.
Thanks!
Hello
Thank you for the detailed report, and please pass our thanks to your hosting team for the query analysis.
Before we go further, three things need to be sorted out.
Please update the plugin.
You are running 3.3.9. The current version is 3.4.3. Several changes since 3.3.9 touch the query building and the caching layer, so any analysis done on 3.3.9 may not describe what the current version actually does. Please update first and re-measure.
Please check the two caching options after the update.
In the plugin settings, Advanced tab:
Cache dynamic recount number for each item in filter - set to Yes
Optimize price filter - set to Yes
Settings can carry over from an older version in an unexpected state, so please confirm both are actually set to Yes after updating, then use the clear buttons next to them once so the caches are rebuilt from scratch on the new version. The price cache in particular stores results per filter combination, so the first visit to a combination will always be slower than the following ones. That is expected. What matters is whether the second and later visits are fast.
Support access.
To continue with this on our side, please add your purchase code with an active support period to the private section of this ticket. These screenshots show where to find it:
https://share.pluginus.net/image/i20230222134241.png
https://share.pluginus.net/image/i20230222134615.png
https://share.pluginus.net/image/i20230222134511.png
If you do not have a licence with an active support period, you can get one here:
https://products-filter.com/downloads
Once that is in place, we can go into the query in detail with you.