MySQL Forums
Forum List  »  Performance

Query optimization and index suggestion needed
Posted by: Pandit P
Date: September 09, 2020 10:01AM

Hello,

I have a complex query which gets built dynamically for a monthly drill down report. My table has 28326649 records and it is increasing daily. Table has 14 partitions for different units.

[

SELECT 'CA Total' as 'target', concat_ws(' - ', client_number, customer) as customerDetails,
IFNULL(ROUND(((IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-09-07' THEN gross_amount ELSE 0 END), 0), 0) / 193758992) * 100), 2), 0) as 'weight',
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-01-31' THEN gross_amount ELSE 0 END), 0), 0) as '2020_January',
CASE WHEN DATEDIFF('2020-01-31', '2020-01-01') < DAY(LAST_DAY('2020-01-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-01-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-01-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-01-31',2)) * Working_Days('2020-01-01','2020-01-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-01-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-01-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-01-31',2)) * Working_Days('2020-01-01','2020-01-31',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-01-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-01-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-01-31',2)) * Working_Days('2020-01-01','2020-01-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-01-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-01-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-01-31',2)) * Working_Days('2020-01-01','2020-01-31',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_January'
,
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-02-01' and '2020-02-29' THEN gross_amount ELSE 0 END), 0), 0) as '2020_February',
CASE WHEN DATEDIFF('2020-02-29', '2020-02-01') < DAY(LAST_DAY('2020-02-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-02-01' and '2020-02-29' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-02-01' and '2019-02-28' THEN gross_amount ELSE 0 END) / Working_Days('2019-02-01','2019-02-28',2)) * Working_Days('2020-02-01','2020-02-29',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-02-01' and '2020-02-29' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-02-01' and '2019-02-28' THEN gross_amount ELSE 0 END) / Working_Days('2019-02-01','2019-02-28',2)) * Working_Days('2020-02-01','2020-02-29',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-02-01' and '2020-02-29' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-02-01' and '2019-02-28' THEN gross_amount ELSE 0 END) / Working_Days('2019-02-01','2019-02-28',2)) * Working_Days('2020-02-01','2020-02-29',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-02-01' and '2020-02-29' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-02-01' and '2019-02-28' THEN gross_amount ELSE 0 END) / Working_Days('2019-02-01','2019-02-28',2)) * Working_Days('2020-02-01','2020-02-29',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_February'
,
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-03-01' and '2020-03-31' THEN gross_amount ELSE 0 END), 0), 0) as '2020_March',
CASE WHEN DATEDIFF('2020-03-31', '2020-03-01') < DAY(LAST_DAY('2020-03-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-03-01' and '2020-03-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-03-01' and '2019-03-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-03-01','2019-03-31',2)) * Working_Days('2020-03-01','2020-03-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-03-01' and '2020-03-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-03-01' and '2019-03-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-03-01','2019-03-31',2)) * Working_Days('2020-03-01','2020-03-31',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-03-01' and '2020-03-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-03-01' and '2019-03-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-03-01','2019-03-31',2)) * Working_Days('2020-03-01','2020-03-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-03-01' and '2020-03-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-03-01' and '2019-03-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-03-01','2019-03-31',2)) * Working_Days('2020-03-01','2020-03-31',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_March'
,
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-04-01' and '2020-09-09' THEN gross_amount ELSE 0 END), 0), 0) as '2020_April',
CASE WHEN DATEDIFF('2020-09-09', '2020-04-01') < DAY(LAST_DAY('2020-04-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-04-01' and '2020-09-09' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-04-01' and '2019-04-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-04-01','2019-04-30',2)) * Working_Days('2020-04-01','2020-09-09',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-04-01' and '2020-09-09' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-04-01' and '2019-04-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-04-01','2019-04-30',2)) * Working_Days('2020-04-01','2020-09-09',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-04-01' and '2020-09-09' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-04-01' and '2019-04-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-04-01','2019-04-30',2)) * Working_Days('2020-04-01','2020-09-09',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-04-01' and '2020-09-09' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-04-01' and '2019-04-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-04-01','2019-04-30',2)) * Working_Days('2020-04-01','2020-09-09',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_April',
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-05-01' and '2020-05-31' THEN gross_amount ELSE 0 END), 0), 0) as '2020_May',
CASE WHEN DATEDIFF('2020-05-31', '2020-05-01') < DAY(LAST_DAY('2020-05-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-05-01' and '2020-05-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-05-01' and '2019-05-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-05-01','2019-05-31',2)) * Working_Days('2020-05-01','2020-05-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-05-01' and '2020-05-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-05-01' and '2019-05-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-05-01','2019-05-31',2)) * Working_Days('2020-05-01','2020-05-31',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-05-01' and '2020-05-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-05-01' and '2019-05-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-05-01','2019-05-31',2)) * Working_Days('2020-05-01','2020-05-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-05-01' and '2020-05-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-05-01' and '2019-05-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-05-01','2019-05-31',2)) * Working_Days('2020-05-01','2020-05-31',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_May',
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-06-01' and '2020-06-30' THEN gross_amount ELSE 0 END), 0), 0) as '2020_June',
CASE WHEN DATEDIFF('2020-06-30', '2020-06-01') < DAY(LAST_DAY('2020-06-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-06-01' and '2020-06-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-06-01' and '2019-06-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-06-01','2019-06-30',2)) * Working_Days('2020-06-01','2020-06-30',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-06-01' and '2020-06-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-06-01' and '2019-06-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-06-01','2019-06-30',2)) * Working_Days('2020-06-01','2020-06-30',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-06-01' and '2020-06-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-06-01' and '2019-06-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-06-01','2019-06-30',2)) * Working_Days('2020-06-01','2020-06-30',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-06-01' and '2020-06-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-06-01' and '2019-06-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-06-01','2019-06-30',2)) * Working_Days('2020-06-01','2020-06-30',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_June',
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-07-01' and '2020-07-31' THEN gross_amount ELSE 0 END), 0), 0) as '2020_July',
CASE WHEN DATEDIFF('2020-07-31', '2020-07-01') < DAY(LAST_DAY('2020-07-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-07-01' and '2020-07-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-07-01' and '2019-07-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-07-01','2019-07-31',2)) * Working_Days('2020-07-01','2020-07-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-07-01' and '2020-07-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-07-01' and '2019-07-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-07-01','2019-07-31',2)) * Working_Days('2020-07-01','2020-07-31',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-07-01' and '2020-07-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-07-01' and '2019-07-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-07-01','2019-07-31',2)) * Working_Days('2020-07-01','2020-07-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-07-01' and '2020-07-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-07-01' and '2019-07-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-07-01','2019-07-31',2)) * Working_Days('2020-07-01','2020-07-31',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_July',
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-08-01' and '2020-08-31' THEN gross_amount ELSE 0 END), 0), 0) as '2020_August',
CASE WHEN DATEDIFF('2020-08-31', '2020-08-01') < DAY(LAST_DAY('2020-08-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-08-01' and '2020-08-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-08-01' and '2019-08-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-08-01','2019-08-31',2)) * Working_Days('2020-08-01','2020-08-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-08-01' and '2020-08-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-08-01' and '2019-08-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-08-01','2019-08-31',2)) * Working_Days('2020-08-01','2020-08-31',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-08-01' and '2020-08-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-08-01' and '2019-08-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-08-01','2019-08-31',2)) * Working_Days('2020-08-01','2020-08-31',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-08-01' and '2020-08-31' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-08-01' and '2019-08-31' THEN gross_amount ELSE 0 END) / Working_Days('2019-08-01','2019-08-31',2)) * Working_Days('2020-08-01','2020-08-31',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_August'
,
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-09-01' and '2020-09-30' THEN gross_amount ELSE 0 END), 0), 0) as '2020_September',
CASE WHEN DATEDIFF('2020-09-30', '2020-09-01') < DAY(LAST_DAY('2020-09-01')) THEN
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-09-01' and '2020-09-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-09-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-09-01','2019-09-30',2)) * Working_Days('2020-09-01','2020-09-07',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-09-01' and '2020-09-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-09-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-09-01','2019-09-30',2)) * Working_Days('2020-09-01','2020-09-07',2))) * 100), 2), 0) END
ELSE
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-09-01' and '2020-09-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-09-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-09-01','2019-09-30',2)) * Working_Days('2020-09-01','2020-09-07',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-09-01' and '2020-09-30' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-09-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-09-01','2019-09-30',2)) * Working_Days('2020-09-01','2020-09-07',2))) * 100), 2), 0) END
END as 'historicalPerformance2020_September' ,
IFNULL(ROUND(SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-09-07' THEN gross_amount ELSE 0 END), 0), 0) as Total,
CASE WHEN (IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-09-07' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-09-30',2)) * Working_Days('2020-01-01','2020-09-07',2))) * 100), 2), 0)) < 0 THEN 9999 ELSE
IFNULL(ROUND(((SUM(CASE WHEN report_date BETWEEN '2020-01-01' and '2020-09-07' THEN gross_amount ELSE 0 END) /
((SUM(CASE WHEN report_date BETWEEN '2019-01-01' and '2019-09-30' THEN gross_amount ELSE 0 END) / Working_Days('2019-01-01','2019-09-30',2)) * Working_Days('2020-01-01','2020-09-07',2))) * 100), 2), 0) END as TotalHistoricalPerf,
0 AS isQty
FROM sales_report WHERE
report_date between '2019-01-01' and '2020-09-07'
group by cust_id order by NULL;


]


I have created a composite index on "cust_id, client_number, customer, report_date, gross_amount" columns. And when execute the Explain plan, I get


id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE sales_report sr_p1,sr_p2,…...,sr_p14 index sr_mv_custid_clntnum_customer_rptdte_grssamnt sr_mv_custid_clntnum_customer_rptdte_grssamnt 1223 (null) 27870117 50 Using where; Using index

Query is taking almost 4 minutes. Though it says index getting used it is not helping me.


Please help.

Thanks
Praveen

Options: ReplyQuote


Subject
Views
Written By
Posted
Query optimization and index suggestion needed
351
September 09, 2020 10:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.