MySQL Forums
Forum List  »  Performance

Re: Query optimization and index suggestion needed
Posted by: Pandit P
Date: September 09, 2020 01:08PM

Thank you Mr. Peter Brawley,

Available RAM is 16 GB

These are the global variables we have

max_connections=420
innodb_buffer_pool_size=6710886400
key_buffer_size=16777216
query_cache_size=131072
tmp_table_size=20971520
read_buffer_size=262144
sort_buffer_size=83886080
event_scheduler=ON
max_allowed_packet=1073741824
innodb_log_file_size=2146435072


Your suggested query

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

took 23 seconds and gave the output of 12405 records


I do not have MySQL 8.0. Still using 5.7 version. I can download and try MySQL 8.0


Is there any suggestions about the global variables?

Regards
Praveen

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query optimization and index suggestion needed
157
September 09, 2020 01:08PM


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.