MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Taking 2.5hrs on 5.6.20 version
Posted by: Peter Brawley
Date: March 04, 2015 12:15PM

If you want humans to read the query, why not format it for human readability?

First impressions ...

(i) the query is invalid because

select a,b,c,d,e,f,g, ..., <aggregates> ... group by a,b

can return arbitrary values for non-grouped and non-aggregated columns c,d,e,f,g.

(ii) each Left Join requires a table scan of the left-sided table, so it might be rewarding to revisit the data model to find a way to turn the Left Joins into Inner Joins, which optimise.

That's significant because, as it stands, the query requires reading 6809*2*41182*2*41182*10476*10308*5966*112 = 27.5 octillion (10^27) data rows, even though the biggest table has just 42K rows!

(iii) the complexity of the Select list and the Join list cries out for a data cube

Are these InnoDB tables? What's innodb_buffer_pool_size? How much RAM is there?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query Taking 2.5hrs on 5.6.20 version
1157
March 04, 2015 12:15PM


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.