Re: Query optimization and indexing - Help needed

[Peter Brawley]

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.