Recommendation for adding MySQL index for slow WooCommerce Memberships query

Question

I have a site based on WooCommerce Memberships with the Teams for WooCommerce Memberships extension. 95% of the time it copes very well despite a large user base (>70,000 users). We get about 10k daily visits with response times under one second.

There are seasonal peaks where users have to complete training and the site can crash under load of concurrent uncached requests.

Using Query Monitor there's one query which sticks out like a sore thumb: 0.0375s (average is < 0.0050s). This is a query that checks:

1. Is the current user a member of a TEAM with an ACTIVE subscription?

2. Do they have permission to view this post?

Here is the query itself:

    SELECT wp_posts.*
    FROM wp_posts
    LEFT JOIN wp_postmeta _teams_pm
    ON wp_posts.ID = _teams_pm.post_id
    AND _teams_pm.meta_key = '_member_id'
    AND _teams_pm.meta_value = 2
    LEFT JOIN wp_usermeta _teams_um
    ON _teams_um.user_id = _teams_pm.meta_value
    AND _teams_um.meta_key = CONCAT( '_wc_memberships_for_teams_team_', wp_posts.ID, '_role' )
    WHERE 1=1
    AND ((wp_posts.post_type = 'wc_memberships_team'
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'acf-disabled'
    OR wp_posts.post_status = 'private')))
    AND ( _teams_um.meta_value    IN('manager', 'member')
    OR wp_posts.post_author = 2 )
    ORDER BY wp_posts.post_date DESC

It is called by:

"WP_Query->get_posts()
wp-includes/class-wp-query.php:3111
WP_Query->query()
wp-includes/class-wp-query.php:3542
WP_Query->__construct()
wp-includes/class-wp-query.php:3653
SkyVerge\W\M\T\Teams_Handler->get_teams()
wp-content/plugins/woocommerce-memberships-for-teams/src/Teams_Handler.php:446
wc_memberships_for_teams_get_teams()
wp-content/plugins/woocommerce-memberships-for-teams/src/Functions/Teams.php:100
ctz_membership_get_user_team_id()
wp-content/plugins/core-functionality/temp/wc_teams.php:603"

Does anyone have any ideas as to what indexes with MySQL could help speed this up? I'm not familiar enough with SQL to know the best place to index.

We have a big spike anticipated on Monday so I'm hoping to get something in place whilst the site is quiet this weekend.

Thanks in advance!

Edit: output of explain, with prefix and db name anonymised:

+----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type  | possible_keys                | key              | key_len | ref                                          | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | wp_posts | NULL       | range | type_status_date,post_author | type_status_date | 164     | NULL                                         | 5556 |   100.00 | Using index condition; Using filesort |
|  1 | SIMPLE      | _teams_pm     | NULL       | ref   | PRIMARY,meta_key,meta_value  | PRIMARY          | 1030    | dbname.wp_posts.ID,const    |    1 |   100.00 | Using where                           |
|  1 | SIMPLE      | _teams_um     | NULL       | ref   | PRIMARY,meta_key             | PRIMARY          | 1030    | dbname._teams_pm.meta_value,func |    1 |   100.00 | Using where                           |
+----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+
3 rows in set, 4 warnings (0.00 sec)

Answer 1

That plugin should help with some of the query.

This might help for wp_posts

INDEX(post_type, post_status, post_author, ID, post_date)

As for multiple indexes on a table... Different queries need different indexes. MySQL will use only one [with rare exceptions] index per table reference. There are essentially 3 queries going on in your query, one against posts and two against postmeta.

The EXPLAIN you provided does not seem to be consistent. Please provide SHOW CREATE TABLE for the two tables so I can figure out what is going on (and better answer your question for Oliver).

The order of columns in an INDEX matters; the order in a WHERE clause does not.

There are two problematic areas in the query; avoiding them may help with performance: CONCAT and OR. This is the OR:

AND ( _teams_um.meta_value    IN('manager', 'member')
      OR wp_posts.post_author = 2 )

No indexing can help. But, because of the needs of the app, it may not be possible to improve on it.

Answer 2

Based on the query, you should create the following indexes, in case you don't already have them:

ALTER TABLE `wp_postmeta` ADD INDEX `wp_postmeta_idx_meta_key_meta_value_post_id` (`meta_key`,`meta_value`,`post_id`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_statu_post_date` (`post_type`,`post_status`,`post_date`);
ALTER TABLE `wp_usermeta` ADD INDEX `wp_usermeta_idx_user_id` (`user_id`);

I also rewrote your query (using EverSQL), mainly to prefer IN clause over OR Conditions and it would be best if you tried to avoid using CONTACT function, as it will not use the relevant index.

SELECT
        wp_posts.* 
    FROM
        wp_posts 
    LEFT JOIN
        wp_postmeta _teams_pm 
            ON wp_posts.ID = _teams_pm.post_id 
            AND _teams_pm.meta_key = '_member_id' 
            AND _teams_pm.meta_value = 2 
    LEFT JOIN
        wp_usermeta _teams_um 
            ON _teams_um.user_id = _teams_pm.meta_value 
            AND _teams_um.meta_key = CONCAT('_wc_memberships_for_teams_team_',
        wp_posts.ID,
        '_role') 
    WHERE
        1 = 1 
        AND (
            (
                wp_posts.post_type = 'wc_memberships_team' 
                AND (
                    wp_posts.post_status IN (
                        'publish', 'acf-disabled', 'private'
                    )
                )
            )
        ) 
        AND (
            _teams_um.meta_value IN (
                'manager', 'member'
            ) 
            OR wp_posts.post_author = 2
        ) 
    ORDER BY
        wp_posts.post_date DESC