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

• Does this issue affect WooCommerce Subscriptions?
  YES 4398-gh-woocommerce/woocommerce-subscriptions
• Does this issue affect WooCommerce Payments?
  YES Searching subscriptions is slow for merchants with a large number of subscriptions woocommerce-payments#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.