Searching subscriptions is slow for merchants with a large number of subscriptions

[Jinksi]

Describe the bug

The Subscriptions search in wp-admin → WooCommerce → Subscriptions is very slow for merchants who have a large number of subscriptions. For example, searching through ~30,000 subscriptions can take as much as 30 seconds.

To Reproduce

1. Generate a large number of subscriptions (method TBC).
2. Navigate to the wp-admin page WooCommerce → Subscriptions.
3. Execute a search using the search field in the top-right of the page, e.g. "john".
4. Wait for the results and observe the duration of the query using Query Monitor. Look for the query called by WCS_Subscription_Data_Store_CPT::search_subscriptions().

Expected behavior

The search query that is called by WCS_Subscription_Data_Store_CPT::search_subscriptions() should take as little time as is reasonably possible.

Actual behavior

The search query that is called by WCS_Subscription_Data_Store_CPT::search_subscriptions() takes longer than is reasonably possible.

Product impact

• [x] Does this issue affect WooCommerce Subscriptions? YES 4398-gh-woocommerce/woocommerce-subscriptions
• [x] Does this issue affect WooCommerce Payments? YES https://github.com/Automattic/woocommerce-payments/issues/4695

Additional context

More info provided by @foliovision in PR #182:

We find that this query in WCS_Subscription_Data_Store_CPT::search_subscriptions() is way too slow:

SELECT DISTINCT p1.post_id
FROM wp_postmeta p1
WHERE p1.meta_value LIKE '%phrase%' AND p1.meta_key IN ('_order_key','_billing_address_index','_shipping_address_index','_billing_email')

Looking as the SQL EXPLAIN I can see that on our client website it has to check 4,000,000 fields. When I split that query into 4 individual queries - one for each meta_key, then it's much faster. The above takes 17 to 20 seconds.

[haszari]

@Jinksi can you open an issue for this on WCPay and WC Subscriptions so we can track and prioritise against products?

[kaushikasomaiya]

One of our merchants reported this as well 7126887-zen

Merchant reported both queries here could be limited to shop_subscription post_type:

https://github.com/Automattic/woocommerce-subscriptions-core/blob/trunk/includes/data-stores/class-wcs-subscription-data-store-cpt.php#L535-L549

Example:

SELECT DISTINCT p1.post_id
FROM wp_postmeta p1
JOIN wp_posts posts ON posts.ID = p1.post_id
WHERE p1.meta_value LIKE '%standard%' 
AND p1.meta_key IN ('_billing_email')
AND posts.post_type = 'shop_subscription'

[OllieJones]

The root performance problem is, of course, the unanchored meta_value LIKE '%searchterm%' SQL predicate. It can't exploit an index, and it searches the meta_value CLOB, so it is necessarily slow -- forcing the DBMS to scan many rows -- when there are lots of subscriptions. Switching to an anchored meta_value LIKE 'searchterm%' search without the leading % wildcard greatly improves performance at the cost of the ability to search for as many substrings.