How to speed up “My Account > Orders Page” on Site with More than 200,000 orders?

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;
}