mysql - 加速具有 12 个子查询的 MySQL 查询的方法

Question

以下查询返回正确的数据，但我想看看是否有更好的方法来执行此操作。查询应返回过去 2 个月内存在记录的 12 个月内每个月的案例数。其想法是获取在相关月份以及前 2 个月中至少一个月内订购的帐户数量。另请注意，data_date 表中的每个值始终是该月的 1 号。

SELECT 
sum( 
        case 
            WHEN    a.data_date = '2013-03-01'
                    and exists( 
                            select 1 from sales mth1 
                            where mth1.client_id = a.client_id
                            and 
                                    data_date BETWEEN '2013-01-01' 
                                    and 
                                    '2013-02-01' 

                    ) 
            then case_qty 
            ELSE 0 END
) AS M1 , 
sum( 
        case 
                WHEN a.data_date = '2013-04-01' 
                        and exists( 
                                select 1 from sales mth2 
                                where mth2.client_id = a.client_id 
                                and mth2.data_date BETWEEN '2013-02-01' 
                                        and 
                                        '2013-03-01' 
                        ) 
                then case_qty 
                ELSE 0 END
    ) AS M2 , 
    sum( case WHEN a.data_date = '2013-05-01' and exists( select 1 from sales mth3 where mth3.client_id = a.client_id and mth3.data_date BETWEEN '2013-03-01' and '2013-04-01' )  then case_qty ELSE 0 END) AS M3 , 
    sum( case WHEN a.data_date = '2013-06-01' and exists( select 1 from sales mth4 where mth4.client_id = a.client_id and mth4.data_date BETWEEN '2013-04-01' and '2013-05-01' )  then case_qty ELSE 0 END) AS M4 , 
    sum( case WHEN a.data_date = '2013-07-01' and exists( select 1 from sales mth5 where mth5.client_id = a.client_id and mth5.data_date BETWEEN '2013-05-01' and '2013-06-01' )  then case_qty ELSE 0 END) AS M5 , 
    sum( case WHEN a.data_date = '2013-08-01' and exists( select 1 from sales mth6 where mth6.client_id = a.client_id and mth6.data_date BETWEEN '2013-06-01' and '2013-07-01' )  then case_qty ELSE 0 END) AS M6 , 
    sum( case WHEN a.data_date = '2013-09-01' and exists( select 1 from sales mth7 where mth7.client_id = a.client_id and mth7.data_date BETWEEN '2013-07-01' and '2013-08-01' )  then case_qty ELSE 0 END) AS M7 , 
    sum( case WHEN a.data_date = '2013-10-01' and exists( select 1 from sales mth8 where mth8.client_id = a.client_id and mth8.data_date BETWEEN '2013-08-01' and '2013-09-01' )  then case_qty ELSE 0 END) AS M8 , 
    sum( case WHEN a.data_date = '2013-11-01' and exists( select 1 from sales mth9 where mth9.client_id = a.client_id and mth9.data_date BETWEEN '2013-09-01' and '2013-10-01' )  then case_qty ELSE 0 END) AS M9 , 
    sum( case WHEN a.data_date = '2013-12-01' and exists( select 1 from sales mth10 where mth10.client_id = a.client_id and mth10.data_date BETWEEN '2013-10-01' and '2013-12-01' )  then case_qty ELSE 0 END) AS M10 , 
    sum( case WHEN a.data_date = '2014-01-01' and exists( select 1 from sales mth11 where mth11.client_id = a.client_id and mth11.data_date BETWEEN '2013-11-01' and '2013-12-01' )  then case_qty ELSE 0 END) AS M11  
FROM sales as a 
INNER JOIN Products AS P ON P.product_id = a.product_id  
WHERE a.client_id IN ('123') 
AND a.data_date BETWEEN '2013-03-01' AND '2013-12-01' AND a.case_qty > 0;

Here's a screen shot of the explain

Here's a screen shot of the indexes

Answer 1

改变

数据日期介于“2013-01-01”和“2013-02-01”之间到 data_date in ('2013-01-01', '2013-01-02',.....,...'2013-02-01')

但我会将整个查询转换为求和情况，然后使用包装器提取我需要的帐户。