WordPress.org

Support

Support » Plugins and Hacks » [Resolved] Extremely slow SELECT queries

[Resolved] Extremely slow SELECT queries

  • 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?

    http://wordpress.org/plugins/woocommerce/

Viewing 1 replies (of 1 total)
  • 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?

Viewing 1 replies (of 1 total)
  • The topic ‘[Resolved] Extremely slow SELECT queries’ is closed to new replies.