MySQL Forums
Forum List  »  Performance

Re: query performance optimization
Posted by: Peter Brawley
Date: January 22, 2016 01:08PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1923
January 18, 2016 04:49AM
805
January 22, 2016 06:29PM
Re: query performance optimization
733
January 22, 2016 01:08PM
762
January 22, 2016 11:10PM


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.