large database queries

Hello,

From our Hosting we are informed that the “Price Based Country” plugin is generating extremely large queries that do not finish and cause overload on the servers. Can you tell us how to solve it?

An example would be this query that reviews 37 million rows:

# User@Host: xxxxx[xxxxx] @ localhost [] Thread_id: 4 Schema: xxxxx QC_hit: No Query_time: 162103.893545 Lock_time: 0.003978 Rows_sent: 0 Rows_examined: 37436284189 Rows_affected: 0 Bytes_sent: 0

SET timestamp=1775896851;

SELECT zones_query.zone_id, posts.ID as post_id
FROM wp_posts posts
CROSS JOIN ((SELECT 'zona-euro' as zone_id, convert('_zona-euro_price_method' using utf8) AS '_price_method_field_name', convert('_zona-euro_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_zona-euro_price' using utf8) AS '_price_field_name', convert('_zona-euro_sale_price' using utf8) AS '_sale_price_field_name', convert('_zona-euro_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_zona-euro_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'zona-no-euro-y-europa-no-ue' as zone_id, convert('_zona-no-euro-y-europa-no-ue_price_method' using utf8) AS '_price_method_field_name', convert('_zona-no-euro-y-europa-no-ue_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_zona-no-euro-y-europa-no-ue_price' using utf8) AS '_price_field_name', convert('_zona-no-euro-y-europa-no-ue_sale_price' using utf8) AS '_sale_price_field_name', convert('_zona-no-euro-y-europa-no-ue_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_zona-no-euro-y-europa-no-ue_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'usa-y-canada' as zone_id, convert('_usa-y-canada_price_method' using utf8) AS '_price_method_field_name', convert('_usa-y-canada_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_usa-y-canada_price' using utf8) AS '_price_field_name', convert('_usa-y-canada_sale_price' using utf8) AS '_sale_price_field_name', convert('_usa-y-canada_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_usa-y-canada_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'latam' as zone_id, convert('_latam_price_method' using utf8) AS '_price_method_field_name', convert('_latam_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_latam_price' using utf8) AS '_price_field_name', convert('_latam_sale_price' using utf8) AS '_sale_price_field_name', convert('_latam_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_latam_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'asia' as zone_id, convert('_asia_price_method' using utf8) AS '_price_method_field_name', convert('_asia_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_asia_price' using utf8) AS '_price_field_name', convert('_asia_sale_price' using utf8) AS '_sale_price_field_name', convert('_asia_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_asia_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'mena' as zone_id, convert('_mena_price_method' using utf8) AS '_price_method_field_name', convert('_mena_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_mena_price' using utf8) AS '_price_field_name', convert('_mena_sale_price' using utf8) AS '_sale_price_field_name', convert('_mena_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_mena_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'africa-sub-sahariana' as zone_id, convert('_africa-sub-sahariana_price_method' using utf8) AS '_price_method_field_name', convert('_africa-sub-sahariana_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_africa-sub-sahariana_price' using utf8) AS '_price_field_name', convert('_africa-sub-sahariana_sale_price' using utf8) AS '_sale_price_field_name', convert('_africa-sub-sahariana_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_africa-sub-sahariana_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate ) UNION (SELECT 'oceania' as zone_id, convert('_oceania_price_method' using utf8) AS '_price_method_field_name', convert('_oceania_sale_price_dates' using utf8) AS '_sale_price_dates_field_name', convert('_oceania_price' using utf8) AS '_price_field_name', convert('_oceania_sale_price' using utf8) AS '_sale_price_field_name', convert('_oceania_sale_price_dates_from' using utf8) AS '_sale_price_dates_from_field_name', convert('_oceania_sale_price_dates_to' using utf8) AS '_sale_price_dates_to_field_name', ('1'+0) AS exchange_rate )) as zones_query
INNER JOIN wp_postmeta meta__price_method
ON meta__price_method.post_id = posts.ID
AND meta__price_method.meta_key = zones_query._price_method_field_name
INNER JOIN wp_postmeta meta__sale_price_dates
ON meta__sale_price_dates.post_id = posts.ID
AND meta__sale_price_dates.meta_key = zones_query._sale_price_dates_field_name
INNER JOIN wp_postmeta meta__sale_price_dates_from
ON meta__sale_price_dates_from.post_id = posts.ID
AND meta__sale_price_dates_from.meta_key = zones_query._sale_price_dates_from_field_name
INNER JOIN wp_postmeta meta__sale_price_dates_to
ON meta__sale_price_dates_to.post_id = posts.ID
AND meta__sale_price_dates_to.meta_key = zones_query._sale_price_dates_to_field_name
INNER JOIN wp_postmeta meta__sale_price
ON meta__sale_price.post_id = posts.ID
AND meta__sale_price.meta_key = zones_query._sale_price_field_name
INNER JOIN wp_postmeta meta__price
ON meta__price.post_id = posts.ID
AND meta__price.meta_key = zones_query._price_field_name
WHERE posts.post_type IN ('product', 'product_variation') AND posts.post_status NOT IN ('trash', 'auto-draft')
AND meta__price_method.meta_value = 'manual'
AND meta__sale_price_dates.meta_value = 'manual'
AND meta__sale_price.meta_value != ''
AND meta__price.meta_value != meta__sale_price.meta_value

                                    AND meta__sale_price_dates_from.meta_value < '1775845852'
                                    AND meta__sale_price_dates_from.meta_value > 0
                                    AND ( meta__sale_price_dates_to.meta_value > '1775845852' OR meta__sale_price_dates_to.meta_value + 0 = 0 )
                                    ORDER BY zones_query.zone_id, posts.ID;

#

I just enabled the “Load product prices in the background” option. Can this help?

Greetings and thanks