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 topic contains 9 replies, has 2 voices.

Last updated by Bas de Kort 12 months ago.

Assigned support staff: Ricardo Alday.

Author Posts
September 27, 2018 at 6:32 am #2769437

Bas de Kort

Hi there,

I'm working on a site with over 73000 orders. Loading the orders page in WooCommerce takes up to 30 seconds because of a query in WCML_Multi_Currency_Orders->get_orders_currencies()

The current query is:
SELECT
m.meta_value AS currency,
COUNT(m.post_id) AS c
FROM
wp_posts p
JOIN wp_postmeta m ON p.ID = m.post_id
WHERE
meta_key = '_order_currency'
AND p.post_type = 'shop_order'
GROUP BY
meta_value

This query generates a temporary table which you can imagine takes ages with a load of records. If you can change the query to:
SELECT
m.meta_value AS currency,
COUNT(DISTINCT m.post_id) AS c
FROM
wp_posts p
JOIN wp_postmeta m ON p.ID = m.post_id
WHERE
meta_key = '_order_currency'
AND p.post_type = 'shop_order'
GROUP BY
meta_value

It does not need to generate a temporary table and running this query alone reduces the query from 28s to 1s which is a big improvement.

I am not sure if there are any downsides by doing this. I seem to get the same results with or without using DISTINCT in the count.

Hope to hear from you soon, and hope you can implement this soon.

Regards,
Bas de Kort

September 28, 2018 at 2:08 am #2772107

Ricardo Alday

Hi Bas,

Thank you for contacting WPML support. I have forwarded your information to our developers to get a more informed opinion on this. I will update you as soon as I have some feedback from them.

Best regards,
Ricardo

September 28, 2018 at 3:21 pm #2774312

Ricardo Alday

Hi Bas,

Thanks for your patience. For testing purposes, can you provide a copy of your site using the Duplicator plugin: https://wordpress.org/plugins/duplicator/.

Once the snapshot is complete, either upload the snapshot ZIP file and the installer.php file to a file sharing service such as Dropbox (and share the link in your reply) or right-click the Duplicator links in your wp-admin and paste in your reply.

Please note: It is important to reduce the size of the package so please exclude some paths like /cache or /uploads and also exclude archives and media files.

Thanks,
Ricardo

October 2, 2018 at 9:27 am #2780845

Bas de Kort

Hi Ricardo,

I am afraid I cannot duplicate the site since it has a lot of orders, and I cannot give you our order database with over 73000 orders in it. I am simply not allowed by european law to do that.

I can however provide you with some screenshots running this query and it's results. Just look at the query time. These queries are run directly on the database without WordPress.

I think in this case using distinct is valid, since it is counting post_id's which are unique in this table by design.

October 2, 2018 at 10:38 pm #2782922

Ricardo Alday

No worries. I escalated this to our developers so they can test the code you provided. I will update you as soon as I have any feedback.

October 10, 2018 at 6:11 am #2803058

Bas de Kort

Hey Ricardo,

Any news on this issue?

October 11, 2018 at 6:17 pm #2809444

Ricardo Alday

Yes, our developers tested this but they saw a completely different outcome. In their tests, adding DISTINCT actually increased the load times. In one test it went from 1 second to 5 seconds.
They ask if you could provide a copy of the database to see if there are other factors at play here but I told them that you are unable to provide one.

Is there any other information you can provide such as server and PHP configuration information? Have you tested with just WPML and WooCommerce active (and the necessary WPML add-ons to integrate with WC).

October 17, 2018 at 5:48 am #2823168

Bas de Kort

I've tested it by running raw queries on the database (to prevent any plugin or theme from sloing things down not caused by this query).

I'll try to test some more off site testing to see if I can find out if or when DISTINCT is going to be faster.

Here is a WC system report:

### WordPress Environment ###

WC Version: 3.4.5
Log Directory Writable: ✔
WP Version: 4.9.8
WP Multisite: –
WP Memory Limit: 4 GB
WP Debug Mode: –
WP Cron: –
Language: nl_NL
External object cache: ✔

### Server Environment ###

Server Info: Apache
PHP Version: 7.2.11
PHP Post Max Size: 16 MB
PHP Time Limit: 0
PHP Max Input Vars: 1000
cURL Version: 7.47.0
OpenSSL/1.0.2g

SUHOSIN Installed: –
MySQL Version: 5.7.23
Max Upload Size: 1 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

### Database ###

WC Database Version: 3.4.5
WC Database Prefix: wp_
MaxMind GeoIP Database: ❌ The MaxMind GeoIP Database does not exist - Geolocation will not function. You can download and install it manually from hidden link to the path: . Scroll down to "Downloads" and download the "MaxMind DB binary
gzipped" file next to "GeoLite2 Country". Please remember to uncompress GeoLite2-Country_xxxxxxxx.tar.gz and upload the GeoLite2-Country.mmdb file only.

Total Database Size: 9532.69MB
Database Data Size: 7819.53MB
Database Index Size: 1713.16MB
wp_woocommerce_sessions: Data: 3803.95MB + Index: 71.77MB
wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB
wp_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB
wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.05MB
wp_woocommerce_order_items: Data: 9.52MB + Index: 4.52MB
wp_woocommerce_order_itemmeta: Data: 87.61MB + Index: 58.17MB
wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB
wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.05MB
wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB
wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.05MB
wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB
wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB
wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB
wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB
wp_ajaxsearchpro: Data: 0.08MB + Index: 0.00MB
wp_ajaxsearchpro_priorities: Data: 0.02MB + Index: 0.02MB
wp_ajaxsearchpro_statistics: Data: 0.02MB + Index: 0.00MB
wp_asp_index: Data: 74.66MB + Index: 90.20MB
wp_asp_synonyms: Data: 0.02MB + Index: 0.03MB
wp_booking_cities: Data: 0.06MB + Index: 0.00MB
wp_booking_hotels: Data: 24.55MB + Index: 0.00MB
wp_booking_hotels_desc: Data: 265.77MB + Index: 0.00MB
wp_booking_hotels_photos: Data: 24.55MB + Index: 0.00MB
wp_booking_hotels_trans: Data: 1.52MB + Index: 0.00MB
wp_booking_hotels_types: Data: 0.02MB + Index: 0.00MB
wp_commentmeta: Data: 3.52MB + Index: 4.03MB
wp_comments: Data: 41.58MB + Index: 33.09MB
wp_failed_jobs: Data: 0.02MB + Index: 0.00MB
wp_gf_addon_feed: Data: 0.02MB + Index: 0.02MB
wp_gf_draft_submissions: Data: 0.02MB + Index: 0.02MB
wp_gf_entry: Data: 8.52MB + Index: 2.03MB
wp_gf_entry_meta: Data: 9.52MB + Index: 15.58MB
wp_gf_entry_notes: Data: 0.02MB + Index: 0.03MB
wp_gf_form: Data: 0.02MB + Index: 0.00MB
wp_gf_form_meta: Data: 0.41MB + Index: 0.00MB
wp_gf_form_view: Data: 0.19MB + Index: 0.16MB
wp_icl_cms_nav_cache: Data: 0.00MB + Index: 0.00MB
wp_icl_content_status: Data: 0.02MB + Index: 0.02MB
wp_icl_core_status: Data: 0.02MB + Index: 0.02MB
wp_icl_flags: Data: 0.02MB + Index: 0.02MB
wp_icl_languages: Data: 0.02MB + Index: 0.03MB
wp_icl_languages_translations: Data: 0.20MB + Index: 0.14MB
wp_icl_locale_map: Data: 0.02MB + Index: 0.02MB
wp_icl_message_status: Data: 0.02MB + Index: 0.03MB
wp_icl_mo_files_domains: Data: 0.02MB + Index: 0.02MB
wp_icl_node: Data: 0.02MB + Index: 0.00MB
wp_icl_reminders: Data: 0.02MB + Index: 0.00MB
wp_icl_strings: Data: 7.52MB + Index: 11.06MB
wp_icl_string_packages: Data: 0.02MB + Index: 0.00MB
wp_icl_string_pages: Data: 29.56MB + Index: 19.58MB
wp_icl_string_positions: Data: 0.11MB + Index: 0.05MB
wp_icl_string_status: Data: 0.02MB + Index: 0.02MB
wp_icl_string_translations: Data: 4.52MB + Index: 2.52MB
wp_icl_string_urls: Data: 0.31MB + Index: 0.31MB
wp_icl_translate: Data: 93.63MB + Index: 17.56MB
wp_icl_translate_job: Data: 0.16MB + Index: 0.11MB
wp_icl_translations: Data: 113.66MB + Index: 301.14MB
wp_icl_translation_batches: Data: 0.08MB + Index: 0.00MB
wp_icl_translation_status: Data: 6.52MB + Index: 1.52MB
wp_links: Data: 0.02MB + Index: 0.02MB
wp_mailchimp_carts: Data: 6.02MB + Index: 0.00MB
wp_mc4wp_log: Data: 1.52MB + Index: 0.00MB
wp_oauth_access_tokens: Data: 20.55MB + Index: 13.58MB
wp_oauth_authorization_codes: Data: 0.02MB + Index: 0.02MB
wp_oauth_jwt: Data: 0.02MB + Index: 0.02MB
wp_oauth_public_keys: Data: 0.02MB + Index: 0.02MB
wp_oauth_refresh_tokens: Data: 9.53MB + Index: 3.50MB
wp_oauth_scopes: Data: 0.02MB + Index: 0.00MB
wp_options: Data: 225.34MB + Index: 128.56MB
wp_pmxe_exports: Data: 2.52MB + Index: 0.00MB
wp_pmxe_google_cats: Data: 0.39MB + Index: 0.00MB
wp_pmxe_posts: Data: 4.52MB + Index: 0.00MB
wp_pmxe_templates: Data: 1.52MB + Index: 0.00MB
wp_pmxi_files: Data: 0.02MB + Index: 0.00MB
wp_pmxi_history: Data: 0.02MB + Index: 0.00MB
wp_pmxi_images: Data: 1.52MB + Index: 0.00MB
wp_pmxi_imports: Data: 0.50MB + Index: 0.00MB
wp_pmxi_posts: Data: 6.52MB + Index: 6.03MB
wp_pmxi_templates: Data: 0.23MB + Index: 0.00MB
wp_popover_ip_cache: Data: 0.02MB + Index: 0.02MB
wp_postmeta: Data: 1885.00MB + Index: 504.92MB
wp_posts: Data: 258.77MB + Index: 126.45MB
wp_queue: Data: 0.02MB + Index: 0.00MB
wp_rg_form: Data: 0.02MB + Index: 0.00MB
wp_rg_form_meta: Data: 0.38MB + Index: 0.00MB
wp_rg_form_view: Data: 0.16MB + Index: 0.16MB
wp_rg_incomplete_submissions: Data: 0.02MB + Index: 0.02MB
wp_rg_lead: Data: 7.52MB + Index: 0.92MB
wp_rg_lead_detail: Data: 7.52MB + Index: 15.06MB
wp_rg_lead_detail_long: Data: 0.02MB + Index: 0.00MB
wp_rg_lead_meta: Data: 0.02MB + Index: 0.05MB
wp_rg_lead_notes: Data: 0.02MB + Index: 0.03MB
wp_termmeta: Data: 0.06MB + Index: 0.03MB
wp_terms: Data: 0.20MB + Index: 0.23MB
wp_term_relationships: Data: 1.52MB + Index: 1.45MB
wp_term_taxonomy: Data: 0.20MB + Index: 0.20MB
wp_toolset_associations: Data: 0.02MB + Index: 0.05MB
wp_toolset_post_guid_id: Data: 0.42MB + Index: 0.28MB
wp_toolset_relationships: Data: 0.02MB + Index: 0.08MB
wp_toolset_type_sets: Data: 0.02MB + Index: 0.03MB
wp_usermeta: Data: 616.77MB + Index: 242.78MB
wp_users: Data: 26.56MB + Index: 28.64MB
wp_wcpdf_invoice_number: Data: 0.02MB + Index: 0.00MB
wp_wc_download_log: Data: 0.02MB + Index: 0.03MB
wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB
wp_wfBadLeechers: Data: 0.02MB + Index: 0.00MB
wp_wfBlockedCommentLog: Data: 0.02MB + Index: 0.00MB
wp_wfBlockedIPLog: Data: 0.11MB + Index: 0.00MB
wp_wfBlocks: Data: 0.02MB + Index: 0.02MB
wp_wfBlocks7: Data: 0.33MB + Index: 0.33MB
wp_wfBlocksAdv: Data: 0.02MB + Index: 0.00MB
wp_wfConfig: Data: 0.05MB + Index: 0.00MB
wp_wfCrawlers: Data: 0.17MB + Index: 0.00MB
wp_wfFileChanges: Data: 0.02MB + Index: 0.00MB
wp_wfFileMods: Data: 0.02MB + Index: 0.00MB
wp_wfHits: Data: 3.52MB + Index: 1.94MB
wp_wfHoover: Data: 0.02MB + Index: 0.02MB
wp_wfIssues: Data: 0.02MB + Index: 0.13MB
wp_wfKnownFileList: Data: 0.02MB + Index: 0.00MB
wp_wfLeechers: Data: 94.70MB + Index: 0.00MB
wp_wfLiveTrafficHuman: Data: 0.02MB + Index: 0.02MB
wp_wfLockedOut: Data: 0.02MB + Index: 0.00MB
wp_wfLocs: Data: 0.02MB + Index: 0.00MB
wp_wfLogins: Data: 6.52MB + Index: 1.91MB
wp_wfNet404s: Data: 1.52MB + Index: 0.52MB
wp_wfNotifications: Data: 0.02MB + Index: 0.00MB
wp_wfPendingIssues: Data: 0.02MB + Index: 0.13MB
wp_wfReverseCache: Data: 0.09MB + Index: 0.00MB
wp_wfScanners: Data: 6.52MB + Index: 0.00MB
wp_wfSNIPCache: Data: 0.42MB + Index: 0.53MB
wp_wfStatus: Data: 0.14MB + Index: 0.09MB
wp_wfThrottleLog: Data: 0.02MB + Index: 0.02MB
wp_wfVulnScanners: Data: 0.02MB + Index: 0.00MB
wp_woocommerce_multisafepay: Data: 0.14MB + Index: 0.00MB
wp_yoast_seo_links: Data: 0.38MB + Index: 0.13MB
wp_yoast_seo_meta: Data: 5.52MB + Index: 0.00MB
_mig_wpmdb_alter_statements: Data: 0.02MB + Index: 0.00MB

### Post Type Counts ###

actie: 4
amn_mi-lite: 2
attachment: 658872
bego_reserv: 8438
bestemming: 60
bnfw_notification: 2
coupon_generator: 13
custom_css: 1
customize_changeset: 1
district: 398
hotel: 5149
hotel-boeken: 4
inc_popup: 1
mc4wp-form: 5
nav_menu_item: 39
page: 284
post: 2222
postman_sent_mail: 250
product: 488
product_variation: 2602
revision: 3493
shop_coupon: 66262
shop_order: 76457
shop_order_refund: 277
testimonial: 5
wo_client: 2
wp-types-group: 13
wp-types-user-group: 1

### Security ###

Secure connection (HTTPS): ✔
Hide errors from visitors: ✔

### Active Plugins (54) ###

Gravity Perks: by Gravity Wiz – 2.0.11
Gravity Forms: by rocketgenius – 2.3.4
WPML Multilingual CMS: by OnTheGoSystems – 4.0.7
Ajax Search Pro: by Ernest Marcinko – 4.14.1
BEGO Affiliate Coupons: by Elmer van Rooijen – 1.0.0
BEGO Author Extension: by Elmer van Rooijen – 1.0.0
BEGO Banner Manager: by Elmer van Rooijen – 1.0.0
BEGO Generated PDF: by Elmer van Rooijen – 1.0.0
Primary Blog Category: by Elmer van Rooijen – 1.0.0
BEGO Reservation Filter: by Klikensteen – 1.0
Better Notifications for WordPress: by Made with Fuel – 1.7
Booking Exit Page: by Elmer van Rooijen – 1.0.0
Booking Hotels Importer: by Klik & Steen – 1.0.0
Booking.com Official Search Box: by Strategic Partnerships Department at Booking.com – 2.1.1
Classic Editor: by WordPress Contributors – 0.4
Duplicate Post: by Enrico Battocchi – 3.2.2
Google Tag Manager for WordPress: by Thomas Geiger – 1.9
Gravity Forms No CAPTCHA reCAPTCHA: by John Parks - Folkhack Studios – 1.0.7
Gravity Forms MailChimp Add-On: by rocketgenius – 4.3
GP Limit Choices: by David Smith – 1.6.24
Heartbeat Control: by Jeff Matson – 1.2.5
IgniteWoo Updater: by IgniteWoo.com – 2.1.12
JM WP Cookie Bar: by Julien Maury – 1.6
WPBakery Page Builder: by Michael M - WPBakery.com – 5.5.4
Mailchimp for WooCommerce: by Mailchimp – 2.1.10 – Not tested with the active version of WooCommerce
MailChimp for WordPress: by ibericode – 4.2.5
MailChimp for WordPress - Premium: by ibericode – 4.5.2
Members: by Justin Tadlock – 2.1.0
Multisafepay: by Multisafepay – 3.2.0
Next Buzz - Gravity Forms - Actiemechaniek Addon: by Next Buzz – 1.0
Postman SMTP: by Jason Hendriks – 1.7.2
Redis Object Cache: by Till Krüss – 1.3.8
Simple 301 Redirects: by Scott Nellé – 1.07
SSL Insecure Content Fixer: by WebAware – 2.7.0
Toolset Types: by OnTheGoSystems – 3.1.1
User Switching: by John Blackbourn & contributors – 1.4.0
WooCommerce CB: by KlikenSteen – 1.0.0 – Not tested with the active version of WooCommerce
WooCommerce Coupon Generator & Importer Pro: by IgniteWoo.com – 2.4.6 – Not tested with the active version of WooCommerce
WooCommerce Multilingual: by OnTheGoSystems – 4.3.6
WooCommerce PDF Invoices & Packing Slips: by Ewout Fernhout – 2.1.10
WP All Import - WooCommerce Add-On: by Soflyy – 1.3.9
WooCommerce: by Automattic – 3.4.5 – 3.4.6 is available
Yoast SEO: by Team Yoast – 8.4
WP All Export Pro: by Soflyy – 1.5.3
WP All Import Pro: by Soflyy – 4.5.5
WP OAuth Server - Pro: by WP OAuth Server – 3.6.0
WP Super Cache: by Automattic – 1.6.4
WP All Import - User Import Add-On: by Soflyy – 1.1.1
WPML All Import: by OnTheGoSystems – 2.0.8
WPML Media: by OnTheGoSystems – 2.3.7
WPML String Translation: by OnTheGoSystems – 2.8.7
WPML Translation Management: by OnTheGoSystems – 2.6.7
YITH Infinite Scrolling Premium: by YITHEMES – 1.1.5
YITH WooCommerce Ajax Product Filter Premium: by YITHEMES – 3.5.1 – Not tested with the active version of WooCommerce

October 18, 2018 at 6:28 pm #2829215

Ricardo Alday

Thanks. So far we have not seen any noticeable difference.

October 19, 2018 at 8:26 am #2831000

Bas de Kort

Hey,

I managed to fix the issue by disabling the MailChimp for WP plugins which somehow invalidated the cache.

So everything is going smoothly now. The query still takes ages, but since it is now properly cached it is no longer this big of an issue.

Thanks for your help so far.