[Escalated to 2nd Tier] Very low performance on backend and frontend (if pages are not cached)

Here i am with all the steps performed and a minimal staging site with a default WordPress theme (and only the necessary plugins active: WPML, WOocommerce and ACF + query monitor obviously).

The restult is always the same slow query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7) ) )
AND ((wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'private')))
AND ( ( ( wpml_translations.language_code = 'en'
OR 0 )
AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation' ) )
OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation' ) )
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 16

I have set up a mariadb-slow.log file to report slow queries and i can also find this one being often slow:

SELECT tax.term_id FROM wp_icl_translations wpml_translations
				JOIN wp_term_taxonomy tax
					ON wpml_translations.element_id = tax.term_taxonomy_id
						AND wpml_translations.element_type = CONCAT('tax_', tax.taxonomy)
		 JOIN wp_terms terms ON terms.term_id = tax.term_id WHERE tax.term_id != tax.term_taxonomy_id LIMIT 0, 201;

I have read that they lack "indexes" in the database, is it right?
Are there specific configurations in WPML that we could optimize to improve performance for a site like ours with 9 languages, multi-currency, multi-domain, and over 800 products per language?

It's really important for us to make it work, I can imagine there are bigger websites than ours, please advice on how they deal with WPML related performance problems.
Our difficulties are mainly in the backend (impossibile to work with products in this situation and requests often go on timeout after Save!) and also in frontend loading times when a page is not cached.

Please find a screenshot of Query monitor with the results.
I'll upload credentials for the staging area, which country are you connecting from? So I can white list it from the firewall.

Thank you for your support.

Also as you can see from the screenshot there are a lot of duplicate queries, they're mainly from WPML: one from wpml-string-translation, a couple from woocommerce-multilingual but nearly all of them are from sitepress-multilingual-cms. The total is 262, quite a lot!

In case you need it, I have a monthly subscription to Tideways, which analyzes all of the traffic and the queries in my server. If needed I will give you the credentials.

Thank you

Hi, hope you have some news for me soon 🙂
Because unfortunately we are still struggling with slow queries.
Thank you

Hi Bigul,
Ok done, updated everything.
Thanks
Matteo