优化MySQL查询：提取同BTC多客户不同最低成本记录

Hey there, let's fix both your slow query and the missing filter for BTCs with identical customer costs. Here's a streamlined, faster solution that meets all your requirements:

WITH customer_min_costs AS (
    -- First, calculate each customer's lowest cost per BTC
    SELECT 
        btc,
        customer_name,
        MIN(cost) AS min_cost
    FROM your_table
    GROUP BY btc, customer_name
),
valid_btcs AS (
    -- Filter BTCs: must have multiple distinct customers, AND not all customers share the same min cost
    SELECT 
        btc
    FROM customer_min_costs
    GROUP BY btc
    HAVING COUNT(DISTINCT customer_name) > 1 
           AND COUNT(DISTINCT min_cost) > 1
)
-- Pull the final results by joining the valid BTCs with their customer min costs
SELECT 
    cmc.btc,
    cmc.customer_name,
    cmc.min_cost
FROM customer_min_costs cmc
INNER JOIN valid_btcs vb 
    ON cmc.btc = vb.btc;

Why this is faster than your original query #

• No more inefficient IN subquery: Your original IN clause can force the database to scan the table multiple times, especially with 80k rows. Using a JOIN instead lets the optimizer use indexes more effectively and avoid redundant work.
• Clear, step-by-step logic: The CTEs (Common Table Expressions) break the problem into simple parts, making it easier for the database to generate an optimal execution plan.

Critical Index Optimization #

To make this query fly, add this composite index to your table—this is the biggest win for performance:

CREATE INDEX idx_btc_customer_cost ON your_table(btc, customer_name, cost);

This index covers every field we need for grouping and calculating MIN(cost), so the database won't have to scan the full table at all. It can pull all required data directly from the index, cutting your query time from over a minute to just a few seconds.

Fixing the Missing Filter #

Your original query didn't account for BTCs where all customers have the same minimum cost (like bf33940). The valid_btcs CTE fixes this with COUNT(DISTINCT min_cost) > 1—this ensures only BTCs with at least two different minimum customer costs are included in the final results.

For older databases that don't support CTEs #

If you're stuck on MySQL 5.x or another system without CTE support, here's the equivalent subquery version:

SELECT 
    cmc.btc,
    cmc.customer_name,
    cmc.min_cost
FROM (
    SELECT 
        btc,
        customer_name,
        MIN(cost) AS min_cost
    FROM your_table
    GROUP BY btc, customer_name
) cmc
INNER JOIN (
    SELECT btc
    FROM (
        SELECT 
            btc,
            MIN(cost) AS min_cost
        FROM your_table
        GROUP BY btc, customer_name
    ) sub
    GROUP BY btc
    HAVING COUNT(DISTINCT customer_name) > 1 
           AND COUNT(DISTINCT min_cost) > 1
) vb ON cmc.btc = vb.btc;

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