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
Subject
Views
Written By
Posted
760
September 09, 2020 10:01AM
439
September 09, 2020 10:34AM
Re: Query optimization and index suggestion needed
400
September 09, 2020 01:08PM
416
September 09, 2020 02:36PM
376
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.