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:
-
Is the current user a member of a TEAM with an ACTIVE subscription?
-
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
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 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)
2
Answers
That plugin should help with some of the query.
This might help for
wp_posts
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 provideSHOW 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 aWHERE
clause does not.There are two problematic areas in the query; avoiding them may help with performance:
CONCAT
andOR
. This is theOR
:No indexing can help. But, because of the needs of the app, it may not be possible to improve on it.
Based on the query, you should create the following indexes, in case you don’t already have them:
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.