MySQL Forums
Forum List  »  Performance

Re: Index creation / Query Optimization : for a group by + SUM query on mysql Parttioned Table
Posted by: Peter Brawley
Date: November 06, 2018 12:59PM

Sometimes tempfile/filesort is unavoidable.

By "extra column" you mean the two columns with correlated subqueries each with a join and complex where condition in its denominator?

They each have to be evaluated for every row returned by the outer query. Sensationally inefficient in two ways, (i) they need to be in the From clause, (ii) they're identical except for a constant divisor so they need to be collapsed to one clause with the divisor exported to the invocation of the clause.

If you can't find a way to move these subqueries to the From clause, precompute them into an intermediary table.

I wouldn't think about indexes till that problem is addressed. When that move is done, let's see the Explain result.

What is the MySQL version?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index creation / Query Optimization : for a group by + SUM query on mysql Parttioned Table
65
November 06, 2018 12:59PM


Sorry, only registered users may post in this forum.

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.