WordPress.org

Ready to get started?Download WordPress

Forums

WooCommerce - excelling eCommerce
[resolved] Extremely slow SELECT queries (2 posts)

  1. Lakjin
    Member
    Posted 9 months 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?

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

  2. Lakjin
    Member
    Posted 9 months 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?

Reply

You must log in to post.

About this Plugin

About this Topic

Tags

No tags yet.