This thread is resolved. Here is a description of the problem and solution.
Problem: The client was experiencing a performance issue with a slow WPML query on their WooCommerce site, which took 3.5 seconds to complete. The query involved a
SELECT COUNT(...)
check that was identified as problematic.
Solution: We recommended the client to update all outdated WPML plugins and to revert the site to a minimal setup to ensure there were no performance issues. We then advised the client to activate only the plugin responsible for creating the Cars and Models to determine if the performance problem would reoccur.
If you're experiencing similar issues, we recommend you try the following steps: 1. Update all outdated WPML plugins. 2. Revert your site to a minimal setup and check for performance improvements. 3. Activate plugins one by one to identify the source of the problem.
Please note that the solution provided might be irrelevant due to being outdated or not applicable to your case. If the issue persists, we highly recommend checking related known issues, verifying the version of the permanent fix, and confirming that you have installed the latest versions of themes and plugins. Should you need further assistance, please do not hesitate to open a new support ticket in the WPML support forum.
This is the technical support forum for WPML - the multilingual WordPress plugin.
Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.
No supporters are available to work today on this forum. Feel free to create tickets and we will handle it as soon as we are online. Thank you for your understanding.
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
I would like to request temporary access (wp-admin and FTP) to your site to take a better look at the issue. You will find the needed fields for this below the comment area when you log in to leave your next reply. The information you will enter is private which means only you and I can see and have access to it.
Our Debugging Procedures
I will be checking various settings in the backend to see if the issue can be resolved. Although I won't be making changes that affect the live site, it is still good practice to back up the site before providing us access. If we do need to debug the site further, I will duplicate the site and work in a separate, local development environment to avoid affecting the live site.
There are a few cache plugins installed which is not recommended because it might cause performance issues on the site. Also, in the screenshot you shared, the 'caller' column points to the plugin W3 Total Cache which is not installed on the site and its Disk cache configuration has caused performance issues in the past.
Could you take a look at the configuration and make sure only one cache plugin is being used and active?
We have WP Rocket and Redis installed which both considered to be very popular and well-made solutions.
Screenshot was taken few days ago when we had W3 total cache installed, as we were testing different caching plugins hoping it will make any changes, however it wont
WP Rocket is a frontend cache and Redis is object/database cache, so they totally can be used together
If you want, we can disable both caches, however it will not solve that query. We've tried it. The problem with the query is not caching plugins but rather WPML queries with the SELECT COUNT(...) = 0 check
Thank you very much for creating the staging site.
I updated all outdated plugins from WPML. Then left the site in a minimal setup and everything is working as expected, with no performance issues. I kindly ask you to activate just the plugin that creates the Cars and Models and then check if the performance problem appeared again.
