Slow WooCommerce reporting SQL queries

  • Resolved markstarzynski

    (@markstarzynski)


    4 years, 3 months ago

    Hello,

    we have a very busy WP/Woo website (currently over 50K orders), and I’ve noticed that our website (particularly the Dashboard) is becoming painfully slow, despite having high spec hosting.

    Inspecting the logs, I can see the following query is very slow (14 seconds):

    # Query_time: 14.195963 Lock_time: 0.000232 Rows_sent: 31 Rows_examined: 1542794
    SET timestamp=1598904625;
    SELECT SUM( meta__order_total.meta_value) as total_sales,SUM( meta__order_shipping.meta_value) as total_shipping,SUM( meta__order_tax.meta_value) as total_tax,SUM( meta__order_shipping_tax.meta_value) as total_shipping_tax, posts.post_date as post_date FROM wp_posts AS posts INNER JOIN wp_postmeta AS meta__order_total ON ( posts.ID = meta__order_total.post_id AND meta__order_total.meta_key = ‘_order_total’ ) INNER JOIN wp_postmeta AS meta__order_shipping ON ( posts.ID = meta__order_shipping.post_id AND meta__order_shipping.meta_key = ‘_order_shipping’ ) INNER JOIN wp_postmeta AS meta__order_tax ON ( posts.ID = meta__order_tax.post_id AND meta__order_tax.meta_key = ‘_order_tax’ ) INNER JOIN wp_postmeta AS meta__order_shipping_tax ON ( posts.ID = meta__order_shipping_tax.post_id AND meta__order_shipping_tax.meta_key = ‘_order_shipping_tax’ )
    WHERE posts.post_type IN ( ‘shop_order’ )

    AND posts.post_status IN ( ‘wc-completed’,’wc-processing’,’wc-on-hold’,’wc-refunded’)

    AND posts.post_date >= ‘2020-08-01 00:00:00’
    AND posts.post_date < ‘2020-09-01 00:00:00’
    GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date) ORDER BY post_date ASC;

    This looks to be related to Woocommerce reporting. Is there any advice you could give me on resolving this?

    Thanks
    Mark

Viewing 3 replies - 1 through 3 (of 3 total)

  • I would use an external reporting solution for your WooCommerce store. Are you using the WooCommerce Admin (Analytics) on the site?

    Plugin Support Tseten a11n

    (@tibetanitech)

    We haven’t heard back from you in a while, so I’m going to mark this as resolved – if you have any further questions, you can start a new thread.

    Thread Starter markstarzynski

    (@markstarzynski)

    After further troubleshooting, I found it was the WooCommerce dashboard widget that was very slow, particularly when using MYISAM tables.

    I resolved it by using the following function to remove the wooCommerce dashboard widget:

    function remove_dashboard_widgets(){
    // remove WooCommerce Dashboard Status
    remove_meta_box( ‘woocommerce_dashboard_status’, ‘dashboard’, ‘normal’);
    }
    add_action(‘wp_user_dashboard_setup’, ‘remove_dashboard_widgets’, 20);
    add_action(‘wp_dashboard_setup’, ‘remove_dashboard_widgets’, 20);

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Slow WooCommerce reporting SQL queries’ is closed to new replies.