MySQL Forums
Forum List  »  Stored Procedures

Re: Query times out when I add extra Where Condition
Posted by: Peter Brawley
Date: January 24, 2020 02:14PM

In addition to performance issues, the query has a validity problem: in queries of the form ...

select a,b,c,d,e,f,g,sum(x) ... group by a,b,c ...

... the values returned in columns d,e,f,g are arbitrary unless d,e,f,g are 1:1 determined by a,b,c, so such a query needs d,e,f,g added to the Group By clause; then, if the query returns exactly what it returned before fixing the Group By clause, you've proved that a,b,c completely determine d,e,f,g; otherwise leave d,e,f,g in the Group By clause to get valid results.

> when I set the JOINS back to LEFT, it only takes 8.440 seconds.
> Thats probably the reason why the JOINS were set to LEFT.

Left Join requires a full table scan on the left-sided table. About the only way your Left Joins could be faster so would be via the query cache (still available in 5.5 (deprecated in 5.7, removed in 8.0).

Explain says MySQL cannot find indexes for most of the query. That's one reason this big query is slow. Another likely reason is configuration settings, eg 107M for innodb_buffer_pool_size is way too low for all except tiny InnoDB loads.

But before proceeding we still need Show Create Table results for all referenced tables.

We also still need the OS and OS version & amount of available RAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query times out when I add extra Where Condition
125
January 24, 2020 02:14PM


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.