When performing joins in SQL where the primary table is very large, query performence can degrade significant. A common optimization technique is to reduce the dataset size early by appyling filters in a subquery before joining. This minimizes the number of rows involved in the join operation and often leads to faster execution.

Consider the following three approaches for joining sales data with commodity metadata:

Approach 1: Direct Join (Slowest – 7.558s)

SELECT 
  A.commodity_id,
  B.commodity_name,
  B.bar_code,
  SUM(A.sell_quantity) AS sellAmount,
  SUM(A.sell_amount) AS sellingPrice,
  SUM(A.sell_amount) - SUM(A.profit) AS purchasePrice,
  SUM(A.profit) AS profit,
  SUM(A.profit) / SUM(A.sell_amount) * 100 AS profitRate
FROM t_commodity_daily_sales A
JOIN t_commodity B ON A.commodity_id = B.commodity_id
WHERE A.shop_id IN (
  SELECT son_shop_id 
  FROM t_shop_ship 
  WHERE main_shop_id = 133 AND son_shop_id != main_shop_id
)
  AND A.create_time >= 1564588800000 
  AND A.create_time < 1572364800000
GROUP BY A.commodity_id
ORDER BY A.commodity_id;

Approach 2: Filtered Subquery as Secondary Table (Faster – 6.446s)

SELECT 
  A.commodity_id,
  A.commodity_name,
  A.bar_code,
  SUM(B.sell_quantity) AS sellAmount,
  SUM(B.sell_amount) AS sellingPrice,
  SUM(B.sell_amount) - SUM(B.profit) AS purchasePrice,
  SUM(B.profit) AS profit,
  SUM(B.profit) / SUM(B.sell_amount) * 100 AS profitRate
FROM t_commodity A
JOIN (
  SELECT commodity_id, sell_quantity, sell_amount, profit
  FROM t_commodity_daily_sales
  WHERE shop_id IN (
    SELECT son_shop_id 
    FROM t_shop_ship 
    WHERE main_shop_id = 133 AND son_shop_id != main_shop_id
  )
    AND create_time >= 1564588800000 
    AND create_time < 1572364800000
) B ON A.commodity_id = B.commodity_id
GROUP BY A.commodity_id
ORDER BY A.commodity_id;

Approach 3: Filtered Subquery as Primary Table (Fastest – 6.402s)

SELECT 
  A.commodity_id,
  B.commodity_name,
  B.bar_code,
  SUM(A.sell_quantity) AS sellAmount,
  SUM(A.sell_amount) AS sellingPrice,
  SUM(A.sell_amount) - SUM(A.profit) AS purchasePrice,
  SUM(A.profit) AS profit,
  SUM(A.profit) / SUM(A.sell_amount) * 100 AS profitRate
FROM (
  SELECT commodity_id, sell_quantity, sell_amount, profit
  FROM t_commodity_daily_sales
  WHERE shop_id IN (
    SELECT son_shop_id 
    FROM t_shop_ship 
    WHERE main_shop_id = 133 AND son_shop_id != main_shop_id
  )
    AND create_time >= 1564588800000 
    AND create_time < 1572364800000
) A
JOIN t_commodity B ON A.commodity_id = B.commodity_id
GROUP BY A.commodity_id
ORDER BY A.commodity_id;