WordPress中两条查询严重拖慢网站，请求优化首条查询

Hey there, let's tackle this performance issue head-on! The problem here is classic N+1 query bloat—you're hitting the database once per player, which adds up fast when you've got 20 players per team. Instead of running 20 separate queries, we can refactor this to a single bulk query, then handle the checks in PHP. Here's how:

Step 1: Collect All Player IDs First #

Instead of looping through each player and running a query immediately, gather all the player IDs you need to check into an array. For example:

// Assume this is your list of players in the team
$player_ids = [101, 102, 103, ...]; // Replace with your actual player IDs
$match = 'your_match_id'; // Your existing match ID variable
$event_id = 'app';

Step 2: Run a Single Bulk Query #

Use a WHERE IN clause to fetch all players who meet the criteria in one go. We'll get a list of player IDs that have the matching match_id and event_id:

global $wpdb;
$tbl = $wpdb->prefix . "my_tournament_matches_events";

// Sanitize the player IDs to prevent SQL injection
$sanitized_player_ids = array_map('intval', $player_ids);
$player_ids_str = implode(',', $sanitized_player_ids);

// Fetch all player IDs that match the conditions
$results = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT player_id FROM $tbl WHERE match_id = %s AND event_id = %s AND player_id IN ($player_ids_str)",
        $match,
        $event_id
    )
);

// Convert the results to an associative array for O(1) lookups
$participating_players = array_flip($results);

Step 3: Check Players in PHP (No More Database Calls) #

Now, when looping through each player, just check if their ID exists in the $participating_players array—this is a fast in-memory check:

foreach ($player_ids as $player) {
    if (isset($participating_players[$player])) {
        echo 'checked="checked"';
    } else {
        // Player isn't participating, do nothing or add default state
    }
}

Bonus: Add a Database Index for Even Faster Queries #

To make this bulk query as fast as possible, add a composite index to your my_tournament_matches_events table. This tells MySQL exactly where to look for the matching rows without scanning the entire table:

CREATE INDEX idx_match_event_player ON wp_my_tournament_matches_events (match_id, event_id, player_id);

(Replace wp_ with your actual WordPress table prefix if it's different.)

Why This Works #

• Instead of 20 separate database calls (each taking ~0.33s), you're making 1 single call—cutting the per-team query time from 6.6s to a fraction of a second.
• In-memory array lookups are nearly instant compared to database round-trips.
• The composite index ensures the database can find the matching rows quickly, even with large datasets.

内容的提问来源于stack exchange，提问作者Giwrgos Rad