Tips to improve WooCommerce member query speed: Optimize MySQL index

Question

I have a WooCommerce membership based website using the Teams for WooCommerce membership extension. Despite having a huge user base (>70,000 users), it performs very well 95% of the time. We receive approximately 10,000 visits per day with response times of under one second.

During peak seasons when users need to complete training, the site may crash due to the load of concurrent uncached requests.

Using the Query Monitor tool, one query caught our attention: 0.0375 seconds (average less than 0.0050 seconds). This query checks for the following:

1. Is the current user a member of a team with an active subscription?

2. Do they have permission to view this article?

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
SkyVergeWMTTeams_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 on indexing how to speed up this query? I'm not familiar enough with SQL to know where the best index placement is.

We are expecting a big spike on Monday, so I hope to be prepared this weekend when the site is quieter.

Thanks in advance!

EDIT: Output of explain, prefix and database name anonymized:

 ---- ------------- --------------- - ---------- ------- ------------------------------- -- ---------------- --------- ------------------------- --------------------- ------ ---------- ------------- --------------------------
| 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 queries.

This may be helpfulwp_posts

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

As for having multiple indexes on a table...different queries require different indexes. MySQL only uses one index per table reference [with rare exceptions]. There are actually 3 queries in your query, one for posts and two for postmeta.

The EXPLAIN you provided seems inconsistent. Please provide the SHOW CREATE TABLE of both tables so I can figure out what's going on (and better answer Oliver's question).

The order of the columns in

INDEX is important; the order in the WHERE clause is not.

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

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

No index can help. However, it may not be possible to improve it due to application requirements.