Background of the issue:
We have an issue with our website performance. When we checked all the c of the CPU, and load spikes are mainly due to some queries, and mostly the queries are from WPML. We have identified several queries that are taking a significant amount of time to execute, which may affect our site's performance. The queries related to fetching posts and their translations that are running slowly are: SELECT 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 (1274,1289,4975) ) ) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND ( ( ( wpml_translations.language_code = 'en' OR ( wpml_translations.language_code = 'en' AND wp_posts.post_type IN ( 'page' ) AND ( ( ( SELECT COUNT(element_id) FROM wp_icl_translations WHERE trid = wpml_translations.trid AND language_code = 'en' ) = 0 ) OR ( ( SELECT COUNT(element_id) FROM wp_icl_translations t2 JOIN wp_posts p ON p.id = t2.element_id WHERE t2.trid = wpml_translations.trid AND t2.language_code = 'en' AND ( p.post_status = 'publish' OR p.post_status = 'private' OR ( p.post_type='attachment' AND p.post_status = 'inherit' ) ) ) = 0 ) ) ) ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','cookielawinfo' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','cookielawinfo' ) ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC. Link to a page where the issue can be seen: hidden link
Symptoms:
Very slow queries and low performance.
Questions:
How can we optimize the queries related to fetching posts and their translations?
What steps can we take to improve the performance of our website when using WPML?
Our 2nd tier checked the issue and suggested that there are few categories that is missing translations like "Sponsored", as well as category base slugs and labels are missing, they suggested to complete it.
Once it's completed, go to WPML > Support > Troubleshooting and run the taxonomy sync option for posts.
I didn't quite understand your explanation regarding the categories. Could you please provide more details? I've attached screenshots of the category list under the "Taxonomy Translation" page for your reference.
Additionally, could you and your technical team recommend any other changes to help resolve this issue?
We intentionally kept those categories untranslated for our English version only, as we don’t need them in other languages. Are you suggesting that this could be contributing to the slowness? If so, this seems like it might be a bug in the plugin. In scenarios like ours, we may choose not to translate all categories into different languages, but are untranslated taxonomies causing issues?
The query from your server appears to be searching for translations of the categories, which is why we recommended trying a translation it might help. However, this is not a definitive solution. To provide a more concrete answer, we would need to replicate the issue, but currently, we are unable to do so on the staging site.
Given this, I suggest you give the translation a try and see if it makes a difference. This could assist us in further debugging the issue.
Additionally, it would be helpful if you could attempt to replicate the issue on the staging server. Without a clear reproduction step, it’s challenging for us to offer a concrete solution.
This query is triggered inside the functions.php file of the client's theme: so go to /wp-content/themes/coinedition/functions.php:362
public function fetch_posts()
{
$args = array(
'post_type' => 'post', //"post" post type
'post_status' => 'publish', //only published articles
'orderby' => 'date', // Order by date
'order' => 'DESC', // Order in descending order (newest first)
'suppress_filters' => false, // Let WPML apply its filters
'lang' => ICL_LANGUAGE_CODE, // Ensure querying posts in the current language
'fields' => 'ids', // Only retrieves post IDs
);
you are using a "lang" argument for the WP_Query and this is a non-valid argument. There is no "lang" in WP_Query, so please remove it.
Thank you for pointing that out. We apologize for the mistake. We mistakenly added the lang argument to the WP_Query while conducting some tests. We have now removed it, as it is indeed not a valid argument.
We appreciate your keen observation and quick response. Please let us know if there's anything else that needs our attention.
We have completed the tasks you mentioned. All categories have been translated into all the languages we support, and we have also run the taxonomy synchronization option in the troubleshooting page.
However, we haven't noticed any significant improvement in performance. Could you please check the website again and provide your feedback?
.png "16072937-Breaking-News-on-Crypto-Blockchain-and-More-Coin-Edition (4).png")
