[resolved] Extremely slow SELECT queries (2 posts)

  1. Ashraf
    Posted 2 years ago #

    Is it normal for SELECT queries to take over 100 seconds for WooCommerce? If not, any reason why it is for me? For example, the following query took over 125 seconds to complete:

    SELECT COUNT( order_items.order_item_id )
           FROM wp_woocommerce_order_items as order_items
           LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id
           LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id
           LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID
           LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
           LEFT JOIN wp_terms AS term USING( term_id )
           WHERE    term.slug IN ('completed','processing','on-hold')
           AND    tax.taxonomy        = 'shop_order_status'
           AND        (
                           itemmeta.meta_key = '_variation_id'
                           AND itemmeta.meta_value = '24790'
                       ) OR (
                           itemmeta.meta_key = '_product_id'
                           AND itemmeta.meta_value = '24790'
           AND    (
                           postmeta.meta_key = '_billing_email'
                           AND postmeta.meta_value IN ( 'XXX@gmail.com' )
                       ) OR (
                           postmeta.meta_key = '_customer_user'
                           AND postmeta.meta_value = '0' AND postmeta.meta_value > 0

    All these slow queries are causing very high MySQL usage (600-700%) which is in turn slowing down the whole server. I have an E3-1240v2 w/ 16GB RAM, so it isn't a slouch. My WooCommerce website gets roughly 10,000 unique hits a day with about 4,000-5,000 downloading something every day (we offer lots of freebies). We are using InnoDB for our tables.

    Any ideas?


  2. Ashraf
    Posted 2 years ago #

    The above mentioned query is what is slowing down my whole server; it is running hundreds of times and I'm not sure why. Apparently it is used when woocommerce_customer_bought_product() is called. When is that function called? Why is it being used so much?

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • WooCommerce
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic


No tags yet.