Re: Query optimization and index suggestion needed
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.
Subject
Views
Written By
Posted
758
September 09, 2020 10:01AM
438
September 09, 2020 10:34AM
399
September 09, 2020 01:08PM
Re: Query optimization and index suggestion needed
414
September 09, 2020 02:36PM
375
September 09, 2020 11:38PM
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.