MySQL合并两个查询遇超时故障，求高效获取交集的优化方案

Hey there! I totally get how frustrating it is when your individual MySQL queries run in a flash, but combining them triggers a 30-second timeout. Let's break down why this might be happening and fix it so you can get that combined intersection results table you need.

First, Let's Pinpoint the Root Cause #

Your two standalone queries each return only a few hundred rows, but when you tried joining or unioning the original tables directly, MySQL was probably doing way more work than necessary. For example, joining the damage and attack tables first (before grouping) could create a massive intermediate dataset—especially if those base tables have millions of rows—before narrowing down to the grouped results. That's almost certainly what's causing the timeout.

Step 1: Add Targeted Indexes (Critical Fix!) #

Before adjusting the query, let's make sure MySQL can run those grouped queries as efficiently as possible. Add these composite indexes to eliminate full-table scans and speed up grouping/filtering:

• For the damage table:

  CREATE INDEX idx_damage_name_weapon_id ON damage(name, weapon, id);
  

• For the attack table:

  CREATE INDEX idx_attack_name_weapon_category_id ON attack(name, weapon, category, id);
  

These indexes cover all columns needed for your GROUP BY, WHERE filter, and COUNT(DISTINCT id)—MySQL can pull the required data directly from the index without reading full table rows (this is called an "index-only scan").

Step 2: Optimize the Combined Query #

Instead of joining the raw base tables, first run your fast grouped queries as subqueries, then join those small result sets (only hundreds of rows each) together. This way, the heavy lifting is done first, and the final join is lightning-fast.

Here's the query to get your intersection (only name + weapon pairs that exist in both tables):

SELECT
    a.shots,
    d.hits,
    a.name,
    a.weapon
FROM (
    -- Your original fast attack query as a subquery
    SELECT
        COUNT(DISTINCT id) AS shots,
        name,
        weapon
    FROM attack
    WHERE category = 'Weapon'
    GROUP BY name, weapon
) a
INNER JOIN (
    -- Your original fast damage query as a subquery
    SELECT
        COUNT(DISTINCT id) AS hits,
        name,
        weapon
    FROM damage
    GROUP BY name, weapon
) d
    ON a.name = d.name
    AND a.weapon = d.weapon
ORDER BY a.name, a.weapon DESC;

INNER JOIN ensures we only keep pairs that exist in both datasets—exactly the intersection you want.

If You Still Run Into Issues #

• Use EXPLAIN before your query to inspect MySQL's execution plan:

  EXPLAIN
  SELECT ... -- paste the combined query here
  

  Look for ALL in the type column—this means a full table scan is happening, which usually indicates your indexes aren't being used. Double-check that you created the indexes correctly.
• If your base tables are extremely large, you could try adding STRAIGHT_JOIN to force MySQL to run the subqueries in the order you specify, but the optimizer should handle this well if your indexes are set up right.

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