Re: Query times out when I add extra Where Condition
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.