[Resolved] Very slow mysql query

This ticket is related to a chat I had a few days ago with one of your colleagues.

We have woocommerce pages on the site, for example like this one - hidden link

And my looking at Query Monitor there are WPML query which takes 3.5sec to complete. Is there a way to solve this?

We are already using IndexMySQL plugin to add indexes and server is quite powerful

It seems that these WPML queries with the SELECT COUNT(...) = 0 check are the worst.

Here is the query:

SELECT cwdposts.ID
FROM cwdposts
INNER JOIN cwdpostmeta
ON ( cwdposts.ID = cwdpostmeta.post_id ) JOIN cwdicl_translations wpml_translations
ON cwdposts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', cwdposts.post_type)
WHERE 1=1
AND ( cwdposts.ID NOT IN (
SELECT object_id
FROM cwdterm_relationships
WHERE term_taxonomy_id IN (7,9) ) )
AND ( ( cwdpostmeta.meta_key = '_price'
AND cwdpostmeta.meta_value != '' ) )
AND ((cwdposts.post_type = 'product'
AND (cwdposts.post_status = 'publish'
OR cwdposts.post_status = 'acf-disabled'
OR cwdposts.post_status = 'private')))
AND ( ( ( wpml_translations.language_code = 'lv'
OR ( wpml_translations.language_code = 'lv'
AND cwdposts.post_type IN ( 'product','portfolio' )
AND ( ( (
SELECT COUNT(element_id)
FROM cwdicl_translations
WHERE trid = wpml_translations.trid
AND language_code = 'lv' ) = 0 )
OR ( (
SELECT COUNT(element_id)
FROM cwdicl_translations t2 JOIN cwdposts p
ON p.id = t2.element_id
WHERE t2.trid = wpml_translations.trid
AND t2.language_code = 'lv'
AND ( p.post_status = 'publish'
OR p.post_status = 'private'
OR ( p.post_type='attachment'
AND p.post_status = 'inherit' ) ) ) = 0 ) ) ) )
AND cwdposts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation','wpcf7_contact_form','portfolio' ) )
OR cwdposts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation','wpcf7_contact_form','portfolio' ) )
GROUP BY cwdposts.ID
ORDER BY cwdposts.menu_order ASC, cwdposts.post_title ASC