针对WooCommerce Memberships慢查询的MySQL索引优化建议请求

Hey there, let's tackle this slow query that's crashing your site during seasonal peaks. First, let's break down what the query is doing and map out targeted indexes to fix it—you'll be ready for Monday's traffic if you implement these over the weekend.

Understanding the Query & EXPLAIN Output #

Your query checks if a user (ID=2) is an author, manager, or member of a published/private wc_memberships_team post. From the EXPLAIN results:

• wp_posts uses the type_status_date index but still does a filesort (noted in the Extra column), which adds unnecessary overhead.
• _teams_pm (postmeta) and _teams_um (usermeta) rely on primary keys, but we can create more focused indexes to speed up the joins.

Targeted Index Recommendations #

Run these one by one during low-traffic hours (make sure to back up your database first!):

1. wp_posts Index
   The current type_status_date index covers post_type, post_status, and post_date, but we need to include post_author to handle the OR wp_posts.post_author = 2 condition and eliminate the filesort.

   CREATE INDEX idx_wp_posts_team_status_author_date ON wp_posts (post_type, post_status, post_author, post_date DESC);
   

   This index lets MySQL quickly filter the right team posts and sort them without extra processing.

2. wp_postmeta Index (for _teams_pm join)
   The query filters _teams_pm by post_id, meta_key = '_member_id', and meta_value = 2. A composite index here will drastically speed up this join:

   CREATE INDEX idx_postmeta_member_id ON wp_postmeta (post_id, meta_key, meta_value);
   

   This lets MySQL jump directly to rows where a post is linked to the user as a team member.

3. wp_usermeta Index (for _teams_um join)
   For the usermeta join, we filter by user_id, meta_value IN ('manager', 'member'), and a dynamic meta_key tied to the team post ID. Optimize this with:

   CREATE INDEX idx_usermeta_team_role ON wp_usermeta (user_id, meta_value, meta_key);
   

   This index first narrows down rows for the user, then filters by role, and finally matches the dynamic team role key—much faster than scanning primary keys alone.

Bonus: Reduce Query Frequency with Caching #

Since team membership roles don't change often, add a cache layer to avoid running this query on every request. For example, update your custom function ctz_membership_get_user_team_id() to use WordPress transients:

function ctz_membership_get_user_team_id( $user_id ) {
    $cache_key = 'user_team_id_' . $user_id;
    $team_id = get_transient( $cache_key );
    
    if ( false === $team_id ) {
        // Your existing code to fetch the team ID here
        $team_id = ...;
        set_transient( $cache_key, $team_id, HOUR_IN_SECONDS );
    }
    
    return $team_id;
}

This will cut down database load significantly during peak times.

Final Notes #

• Test these indexes on a staging site first if possible, to confirm they improve query speed.
• InnoDB supports online DDL, so creating these indexes won't lock your site for long, but doing it over the weekend when traffic is low is still safest.

内容的提问来源于stack exchange，提问作者Curious Toad