The situation is a WooCommerce shop with 3000+ products. The shop is restricted to members (5000+), and members have access to limited products. The problem is querying the user specific product IDs is a huge performance bottleneck.
Attempt 1 (query takes 22 seconds)
Search through the list of members attached to the product in a meta value:
'meta_query' => array( array( 'key' => 'product-members', 'value' => ':"' . $member_id . '";', 'compare' => 'LIKE', ), ),
Attempt 2 (query takes 17 seconds)
Cache the list of product IDs that a user is allowed to see, then do all product queries with:
'post__in' => $post_ids,
Without the member specificity the query takes 150ms… What other ways can I try to avoid this performance hit? How can I save the product/member relation differently so it's easier to query?