MySQL：自连接表的SUM()聚合查询优化方案咨询

Hey there! Let's tackle this query performance issue head-on. The problem with your current approach is that when the subquery returns a large number of SKUs, the outer query ends up scanning the entire Stock_Level table to compute groupings—even though you only care about SKUs that had recent updates. Here are several optimized rewrite options to fix this:

1. Replace IN with EXISTS (Semi-Join Optimization) #

EXISTS is far more efficient than IN for large result sets because it uses a semi-join: it stops searching as soon as it finds a matching SKU in the subquery, instead of building and scanning a full list of SKUs first. Here's the rewritten query:

SELECT 
    sl.sku,
    SUM(sl.stock_level - sl.allocated) AS available,
    SUM(sl.on_order) AS on_order,
    MAX(sl.date_booked_in) AS date_booked_in,
    MAX(sl.date_last_sold) AS date_last_sold,
    MAX(sl.date_last_stock_take) AS date_last_stock -- Fixed your column name typo here
FROM Stock_Level sl
WHERE EXISTS (
    SELECT 1 
    FROM Stock_Level sl_recent
    WHERE sl_recent.sku = sl.sku
      AND sl_recent.status_since >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
)
GROUP BY sl.sku;

Why this works better: #

• The subquery leverages your existing status_since index to quickly locate SKUs with recent updates.
• The semi-join logic avoids creating a large temporary table of SKUs, saving memory and processing overhead.

2. Self-Join with Distinct Recent SKUs #

Another approach is to first isolate the distinct set of recently updated SKUs, then join that subset back to the main table. This reduces the total number of rows the outer query needs to process for aggregation:

Using CTE (Modern Databases) #

WITH recent_skus AS (
    SELECT DISTINCT sku 
    FROM Stock_Level
    WHERE status_since >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
)
SELECT 
    sl.sku,
    SUM(sl.stock_level - sl.allocated) AS available,
    SUM(sl.on_order) AS on_order,
    MAX(sl.date_booked_in) AS date_booked_in,
    MAX(sl.date_last_sold) AS date_last_sold,
    MAX(sl.date_last_stock_take) AS date_last_stock
FROM Stock_Level sl
JOIN recent_skus rs ON sl.sku = rs.sku
GROUP BY sl.sku;

Using Derived Table (Older Databases) #

If your database doesn't support CTEs (e.g., older MySQL versions), use a derived table instead:

SELECT 
    sl.sku,
    SUM(sl.stock_level - sl.allocated) AS available,
    SUM(sl.on_order) AS on_order,
    MAX(sl.date_booked_in) AS date_booked_in,
    MAX(sl.date_last_sold) AS date_last_sold,
    MAX(sl.date_last_stock_take) AS date_last_stock
FROM Stock_Level sl
JOIN (
    SELECT DISTINCT sku 
    FROM Stock_Level
    WHERE status_since >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
) rs ON sl.sku = rs.sku
GROUP BY sl.sku;

Why this works better: #

• The initial subquery/CTE uses the status_since index to fetch only the distinct SKUs with recent updates, creating a small dataset.
• The join filters the main table down to just the SKUs you care about before running aggregations, drastically reducing the number of rows processed.

3. Index Tuning for Maximum Performance #

To squeeze even more speed out of these queries, consider adding these composite indexes:

1. Index for recent SKU lookup: CREATE INDEX idx_status_since_sku ON Stock_Level (status_since, sku);
   This lets the database retrieve distinct recent SKUs without scanning full row data.
2. Index for aggregation: CREATE INDEX idx_sku_aggregation ON Stock_Level (sku, stock_level, allocated, on_order, date_booked_in, date_last_sold, date_last_stock_take);
   This enables an index-only scan for the aggregation step, meaning the database doesn't need to access the actual table data at all.

Quick Note: #

I fixed a small typo in your original query—you referenced date_last_stock but the actual column is date_last_stock_take. Make sure to keep that consistent in your final code!

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