If you ever find yourself in a situation where your subscription order id cache (_subscription_renewal_order_ids_cache) is not building properly, it could be because the database is timing out.

If you haven’t yet made the transition to the High-Performance Order Storage, WordPress needs to do a very taxing meta query in `get_related_order_ids` in order to rebuild the cache.

They way that we drastically improved the performance (from over 60s to less than 1s) is by adding an author__in clause to the query. On our site, we’re using another workaround that assigns the customer id in the post_author column for every order and subscription. Full details here.

This allows us to query for just orders created by the customer using an indexed field, which drastically reduces the scope of what needs to be queried.

Here’s some example code showing how you might implement this query filter in get_related_order_ids.

add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'devpress_woocommerce_related_orders_meta_query', 20, 2 );

/**
* Hook into meta query for related orders, and add customer ID to author__in if found.
*
* @param array $query      Valid WP_Query args generated by WooCommerce.
* @param array $query_vars Query vars from a WC_Order_Query.
*
* @return array
*/
public function woocommerce_related_orders_meta_query( $query, $query_vars ) {
	$check_customer = devpress_has_related_order_meta( $query );

	if ( $check_customer ) {
		$query['author__in'] = [ $check_customer ];
	}

	return $query;
}

/**
* Check to see if we're pulling related orders via metadata, which causes slow queries, and
* which looks for one of: _subscription_renewal, _subscription_resubscribe, _subscription_switch.
* Return the customer ID, if found.
*
* @param array $args
*
* @return bool|int
*/
public function has_related_order_meta( $args ) {
	if ( ! isset( $args['meta_query'] ) ) {
		return false;
	}

	$subscription_id    = '';
	$related_order_meta = [
		'_subscription_renewal',
		'_subscription_resubscribe',
		'_subscription_switch',
	];

	foreach ( $args['meta_query'] as $meta_query ) {
		if ( ! isset( $meta_query['key'] ) ) {
			continue;
		}

		if ( in_array( $meta_query['key'], $related_order_meta, true ) ) {
			$subscription_id = $meta_query['value'];
			break;
		}
	}

	if ( ! $subscription_id ) {
		return false;
	}

	$subscription = wcs_get_subscription( $subscription_id );
	if ( $subscription ) {
		return $subscription->get_customer_id();
	}

	return false;
}

Feel free to reach out if you’ve hit this issue and have any questions!