0

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?

0

Depending on whether or not your member and product tables are changed often, the MySQL Query Cache might be an interesting feature for you.

It caches the result sets for queries once they are processed and delivers them from the cache for some time. You can set the refresh time to a longer span, if your tables change less often, so you will benefit from the cached results for longer.

Refreshes to the cache could then be done by a script which is triggered from a Cronjob or something else. I use something similar to create full page cache versions of a website and only refresh the cache every hour or so.

StackOverflow has some interesting questions on this topic, too. You might start here: PHP Best way to cache MySQL results?

  • Hi. Thanks for the suggestion. The tables do change regularly however, and since every user has a unique situation and every shop search/filter action results in a new query I imagine a cached result can rarely be re-used. – Berend Feb 15 '17 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.