Re: query performance optimization

[Peter Brawley]

Devrishi,

I took the liberty of adding in code tags to improve the readability of your Explain output.

Of course what stands out is that the engine can't find an index for the "primary" query, so it has to read 463,000 rows. With huge tables, that's a lot of disk hits.

Could you please post a Visual Explain Plan from WorkBench (https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html) for this query?

To explore optimisation, I'd run the following test versions of the query, and Explain Extended, on a small enough data subset such that iterative testing isn't prohibitively slow ...

1. Remove Select expressions from the Select list which are non-grouping and non-grouped (to make the query valid, you need to either do that, or add those columns to the Group By list)

2. Drop the Having() clause

3. Drop the In() Group By subclause

4. Drop the Where clause

5. Change the Left Joins to Inner Joins

My guess is, table size and left joins contribute much of the trouble here, but these tests should go toward clarifying that.