0

On my account>orders page, the slowest query is the following:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( ( ( wp_postmeta.meta_key = '_customer_user'
AND wp_postmeta.meta_value IN ('1') ) ) )
AND wp_posts.post_type IN ('shop_order', 'shop_order_refund')
AND ((wp_posts.post_status = 'wc-pending'
OR wp_posts.post_status = 'wc-processing'
OR wp_posts.post_status = 'wc-on-hold'
OR wp_posts.post_status = 'wc-completed'
OR wp_posts.post_status = 'wc-cancelled'
OR wp_posts.post_status = 'wc-refunded'
OR wp_posts.post_status = 'wc-failed'
OR wp_posts.post_status = 'wc-cancel-request'
OR wp_posts.post_status = 'wc-printing'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

The page takes 7-10 seconds to load. This is most probably due to having more than 200,000 orders in the database. I have tried limiting the post_status to 3 statuses instead of search through orders with all statuses but that didn't help. How to speed up the my account>orders page?

  • It's probably matter of some plugin adding resources to that page. You don't query for 200,000 when the page load. Have you tried query monitor? – huraji Sep 24 '19 at 19:50
  • This query is initiated by woocommerce. – dc09 Sep 24 '19 at 19:51
  • I repeat, I'd work on your assets and unnecessary plugins before touching the woocommerce queries which are already tested on few stores around. You can also think to a good cache database engine like memecached or redis stackoverflow.com/questions/10558465/memcached-vs-redis. – huraji Sep 24 '19 at 20:12
  • Issue being faced with all plugins except woocommerce disabled. – dc09 Sep 24 '19 at 21:23
0

This is an issue with the WooCommerce plugin. As of the 3.7.0 version the My Account > Orders page uses an inefficient query to get the customer's orders. With 200,000 orders in the store you probably have 50x that many rows in the wp_postmeta table. I just encounterd this same issue on a site with 600K posts and 35M postmeta rows.

Source of the query

The My Account shortcode runs do_action('my_account_shortcode') which then fires woocommerce_account_content(). This then fires do_action(woocommerce_account_orders_endpoint) which calls woocommerce_accont_orders().

The wc_get_orders() call on line 3050 is what is calling the query from your original question.

Potential solution

1. Change the query to get customer's orders

To change this query I added a woocommerce_account_orders() function to the theme that would take precidence over the WooCommerce one and called a simpler query:

global $wpdb;
$user_id = get_current_user_id();
$sql     = "SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_key = '_customer_user' AND wp_postmeta.meta_value = " . $user_id . " ORDER BY wp_postmeta.post_id DESC";
$results = $wpdb->get_results( $sql );
$orders  = wp_list_pluck( $results, 'post_id' );

// Setup an array to send to the template.
$customer_orders['orders'] = $orders;
$customer_orders['max_num_pages'] = 1;

wc_get_template(
    'myaccount/orders.php',
        array(
        'current_page'    => absint( $current_page ),
        'customer_orders' => $customer_orders,
        'has_orders'      => 0 < count( $customer_orders['orders'] ),
    )
);

Possible improvements to the above code:

  1. I know using $wpdb is frowned upon, but this was a quick fix that worked to show a reduced database load from that page.
  2. This does not page results. I tested it with a customer who had 75 orders and the page loaded in sub 1 second.

2. Override the orders template

You will also need to override the 'myaccount/orders.php' template in your theme and treat $customer_orders as an array instead of an object.

In this file change

foreach ( $customer_orders->orders as $customer_order ) {

to this

foreach ( $customer_orders['orders'] as $customer_order ) {

Finally, the original query pulls posts of type 'shop_order' and 'shop_order_refund'. Your store might have other order types such as subscriptions.

Inside the template loop you can add a check after wc_get_order() such as:

// skip if this is a subscription 
if ( wcs_is_subscription( $order ) ){
   continue;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.