CREATE TEMPORARY TABLE t_sku_analyze_temp AS ( SELECT sku, SUM(daily_sell_price) AS sell_price, SUM(profit) AS profit FROM t_sku_analyze2023 WHERE statistics_time >= '2023-04-01 00:00:00' AND statistics_time <= '2023-04-30 00:00:00' GROUP BY sku );增加SKU索引

可以使用 ALTER TABLE 语句为虚拟表添加索引，例如为 t_sku_analyze_temp 中的 sku 列添加索引，可以使用以下语句：

ALTER TABLE t_sku_analyze_temp ADD INDEX idx_sku (sku);

这样可以加速对该列的查询和排序操作，提高查询性能。需要注意的是，添加索引会占用额外的存储空间，并且会对数据的插入、更新和删除等操作产生一定的影响，因此需要根据实际情况权衡是否添加索引。

相关问题

给虚拟表CREATE TEMPORARY TABLE t_sku_analyze_temp AS ( SELECT sku, SUM(daily_sell_price) AS sell_price, SUM(profit) AS profit FROM t_sku_analyze2023 WHERE statistics_time >= '2023-04-01 00:00:00' AND statistics_time <= '2023-04-30 00:00:00' GROUP BY sku );增加SKU索引

可以使用 ALTER TABLE 语句为虚拟表添加索引，例如为 t_sku_analyze_temp 中的 sku 列添加索引，可以使用以下语句：

ALTER TABLE t_sku_analyze_temp ADD INDEX idx_sku (sku);

这样可以加速对该列的查询和排序操作，提高查询性能。需要注意的是，添加索引会占用额外的存储空间，并且会对数据的插入、更新和删除等操作产生一定的影响，因此需要根据实际情况权衡是否添加索引。

使用join优化 CREATE TEMPORARY TABLE t_sku_analyze_temp ( sku VARCHAR(225) PRIMARY KEY, sell_price DECIMAL(10, 2), profit DECIMAL(10, 2), sku_cost DECIMAL(10, 2), INDEX idx_sku (sku) ) ENGINE=InnoDB SELECT sku, SUM(daily_sell_price) AS sell_price, SUM(profit) AS profit,sum(sku_cost) as sku_cost, FROM t_sku_analyze2023 WHERE statistics_time >= '2023-04-01 00:00:00' AND statistics_time <= '2023-04-30 00:00:00' GROUP BY sku; CREATE TEMPORARY TABLE t_sku_refund_temp ( sku VARCHAR(225) PRIMARY KEY, refund_monry DECIMAL(10, 2), INDEX idx_sku (sku) ) ENGINE=InnoDB SELECT sku, SUM(refund_monry) AS refund_monry FROM t_sku_refund_analysis2023 WHERE refund_month = '2023-04' GROUP BY sku; SELECT t3.sku,sell_price,profit,refund_monry FROM t_sku_analyze_temp t3 LEFT JOIN t_sku_refund_temp t4 ON t3.sku = t4.sku; GROUP BY sku

Here is the optimized query using JOIN:

SELECT
t1.sku,
SUM(t1.daily_sell_price) AS sell_price,
SUM(t1.profit) AS profit,
SUM(t1.sku_cost) AS sku_cost,
SUM(t2.refund_monry) AS refund_monry
FROM t_sku_analyze2023 t1
LEFT JOIN t_sku_refund_analysis2023 t2 ON t1.sku = t2.sku AND t2.refund_month = '2023-04'
WHERE t1.statistics_time >= '2023-04-01 00:00:00' AND t1.statistics_time <= '2023-04-30 00:00:00'
GROUP BY t1.sku;

In this query, we combine the two temporary tables into a single query using a LEFT JOIN. We also move the WHERE clause to the main query to filter the data before joining, which can improve performance. Finally, we use SUM() functions to calculate the aggregate values instead of creating temporary tables.