mysql - 优化具有子查询的 MYSQL 查询

Question

/*伙计们谁能帮我优化下面提到的查询。在子查询中，我将发货日期和(产品的)创建日期之间的差异计算为 time_to_shift 。然后在外部查询中，我想根据 time_to_shift 平板计算订单。下面给出的查询有效但需要很多时间。谁能告诉我如何在子查询上创建索引并在外部查询中使用它们。这也可能解决问题。*/

SELECT DATE_FORMAT(b.ship_date,'%Y-%m')  AS MONTH, 
COUNT(*) AS Total_units_shipped, 
ROUND(100*COUNT(CASE WHEN time_to_ship <= 0 THEN time_to_ship END)/COUNT(*),2) AS '0 day',
ROUND(100*COUNT(CASE WHEN time_to_ship =1 THEN time_to_ship END)/COUNT(*),2) AS '1 day',
ROUND(100*COUNT(CASE WHEN time_to_ship> 1  AND time_to_ship<= 2 THEN time_to_ship END)/COUNT(*),2) AS '1-2 days',
ROUND(100*COUNT(CASE WHEN time_to_ship >2 AND time_to_ship <= 4 THEN time_to_ship END)/COUNT(*),2) AS '3-4 days',
ROUND(100*COUNT(CASE WHEN time_to_ship > 4 AND time_to_ship <= 10 THEN time_to_ship END)/COUNT(*),2) AS '5-10 days', 
ROUND(100*COUNT(CASE WHEN time_to_ship > 10 AND time_to_ship <= 20 THEN time_to_ship END)/COUNT(*),2) AS '11-20 days', 
ROUND(100*COUNT(CASE WHEN time_to_ship >20 THEN time_to_ship END)/COUNT(*),2) AS '>20 days' 
FROM(
   SELECT 
   DATE(fsp.sp_date_shipped) AS ship_date,
   CASE WHEN WEEKDAY(DATE(fso.soi_date_created)) = 5 THEN 
    (DATEDIFF(DATE(fsp.sph_date_shipped),DATE(fso.soi_date_created)) -1   ) 
      ELSE
       (DATEDIFF(DATE(fsp.sph_date_shipped),DATE(fso.soi_date_created)) ) 
    END
      AS Time_to_Ship 
    FROM dwh.f_suborders_oms fso 
    JOIN dwh.f_shipping_package fsp ON fso.soi_shipping_package_id=fsp.sp_shipping_package_id
    WHERE    fsp.sp_date_shipped BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH ),'%Y%m01000000')
    AND DATE_FORMAT( CURDATE(),'%Y%m01000000')
 )b  
GROUP BY 1
LIMIT 1000000 ;

Answer 1

什么是 fsp.sp_date_shipped 列类型？看起来问题是

BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH ),'%Y%m01000000')
AND DATE_FORMAT( CURDATE(),'%Y%m01000000'). 

尽量避免 DATE_FORMAT 并使用日期而不是格式化输出来让索引工作而不是全扫描