MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query optimization and indexing - Help needed
Posted by: Peter Brawley
Date: June 13, 2019 12:55PM

MySQL version?

Let's see the result of ...

Show Create Table sales_report;

Assuming it's an InnoDB table, what's innodb_buffer_pool_size? How much RAM is available to MySQL?

MySQL can usually use one index per tableref in a query.

Without seeing the table DDL, I can see a few subclause improvements ...

- simplify constant exp0ressions eg ... YEAR('2019-06-11') --> '2019', (report_date >= '2019-01-01' AND report_date <= '2019-12-31') --> left(report_date,4)='2019'

- lose Having, move its condition to the top of the Where clause

- some of the long IN(...) lists cry out for simplification to joins to intermediate tables

Otherwise, to see where to start, I'd benchmark the query with and without the complicated conditions in the Select list, and with and without the complicated conditions in the Where clause.

If even after such optimisations the query elicits swapping, you may need to reconfigure memory use and/or add RAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query optimization and indexing - Help needed
127
June 13, 2019 12:55PM


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.