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.

This thread is resolved. Here is a description of the problem and solution.

Problem: When WCML is enabled, many pages on admin are using the function WCML_Multi_Currency_Orders->get_orders_currencies()

This function, is making a query that in our case takes anywhere between 2.5-4 seconds to run if there are hundreds of thousands of orders.

Solution: Installing a caching plugin will help to resolve these slow queries.

This topic contains 4 replies, has 2 voices.

Last updated by George L. 1 month, 1 week ago.

Assigned support staff: Lauren.

Author Posts
September 9, 2019 at 4:35 pm #4538927

George L.

When WCML is enabled, many pages on admin are using the function WCML_Multi_Currency_Orders->get_orders_currencies()

This function, is making a query that in our case takes anywhere between 2.5-4 seconds to run if there are hundreds of thousands of orders.

SELECT LEFT(m.meta_value, 3) AS currency, COUNT(*) AS c
FROM wp_postmeta m
INNER JOIN wp_posts p on p.ID = m.post_id
WHERE m.meta_key='_order_currency'
AND p.post_type='shop_order'
GROUP BY currency

I believe this query is unnecessary across all pages, but even if it's necessary, it would require some improvements.

At the moment, this query cannot be cached, because as stated in the manual, any query that uses TEMPORARY table, cannot be cached.

As shown in the attached "Explain" query, this query uses a temporary table and therefore cannot be cached.

As explained in the docs, the issue arises from the use of "GROUP BY". If you run the same query without it, temporary tables are not used and the query can be cached correctly.

Unless there's a specific reason you require the use of "GROUP BY", it would be great if you could remove it, or at least move it in PHP.

Note: I'm aware MySQL has removed support for Query Cache on v8, but MariaDB still uses it. If you'd rather not write queries that work with Query Cache, please advise of the drop-in alternatives.

Note 2: I see you check for Object Cache before running this query, but we don't currently use it.

References
hidden link
hidden link
hidden link
hidden link

Relevant topics
https://wpml.org/forums/topic/major-improvement-in-query-get_orders_currencies/
https://wpml.org/forums/topic/multicurrency-slowing-down-backend-2/

p.s I cannot provide access to our server or data, but I can give you access to a blank staging environment. This is not necessary, as the query is called in many pages (for example /wp-admin/edit.php?post_type=shop_order) even on vanilla installations.

p.s.2 Our application and SQL servers have been optimised for speed and the issue does not originate from them.

September 11, 2019 at 2:09 pm #4554009

Lauren
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

Thanks for your patience. One of our developers has taken a look and said that the grouping is done so that we can know all the currencies with the number of orders. Also, temporary tables are an optimal solution.

His suggestion is to try using a caching plugin to see if that would resolve the issue for you. To review which caching plugins are compatible with WPML, please see https://wpml.org/documentation/plugins-compatibility/

Please let me know if a caching plugin is able to resolve the issue for you. Thanks!

September 11, 2019 at 2:19 pm #4554139

George L.

We have FastCGI cache on Nginx, so we don't need a third party plugin to do caching.

Can the developer explain their comment about temporary tables being an "optimal solution"? While trying to optimise database queries, it's good practice to reduce the unnecessary overhead created by the temporary tables where possible.

The query results are identical regardless of the use of "GROUP BY", but removing it, allows for query cache

September 12, 2019 at 11:57 am #4560737

Lauren
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

Our developer asked to please reference this link in regards to caching: https://codex.wordpress.org/Class_Reference/WP_Object_Cache#Persistent_Cache_Plugins

If we remove the "group by" then the count will return 1 for each currency and repeating currencies. If you have a better suggestion for this query without affecting the outcome then please feel free to let us know

This link https://stackoverflow.com/a/3856313 explains how joins are better than a subquery.

September 13, 2019 at 9:51 am #4567225

George L.

I'll look for a workaround that doesn't involve changing the query