MySQL Forums
Forum List  »  Performance

Re: Query optimization and index suggestion needed
Posted by: Peter Brawley
Date: September 09, 2020 02:36PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query optimization and index suggestion needed
142
September 09, 2020 02:36PM


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.