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?

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?

• woocommerce-offtopic
• sql

Share Improve this question asked Sep 24, 2019 at 19:15 dc09dc09 19522 silver badges1414 bronze badges 4

• 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? – middlelady Commented Sep 24, 2019 at 19:50
• This query is initiated by woocommerce. – dc09 Commented Sep 24, 2019 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/questions/10558465/memcached-vs-redis. – middlelady Commented Sep 24, 2019 at 20:12
• Issue being faced with all plugins except woocommerce disabled. – dc09 Commented Sep 24, 2019 at 21:23

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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;
}