Re: Query optimization and index suggestion needed

[Peter Brawley]

1 Most execution time for this query is taken up by Select expression computations.

How many secs for this?

SELECT cust_id, Left(report_date,7) as YrMo, SUM(gross_amount)
FROM sales_report FORCE INDEX index sr_mv_custid_clntnum_customer_rptdte_grssamnt
WHERE report_date between '2019-01-01' and '2020-09-07'
GROUP BY cust_id, YrMo ;

2 Working_Days() is doing stupid numbers of repeat identical calculations ... an easy candidate for optimisation ... there can't be more than a few values relevant to this query, so the numbers retrieved by this func ought to precomputed.

3 I'd want innodb_buffer_pool_size=75% of RAM available to MySQL.

4 Is the query inducing swapping?



Edited 1 time(s). Last edit at 09/09/2020 02:41PM by Peter Brawley.