Re: Moving Average Query Not Completing

[Peter Brawley]

Correlated subqueries don't perform well. Try moving it to the From clause, something like ...

select a.species, a.date, a.qty_taken, b.5yrAvg
from global a 
join (   
  select species, sum(y.qty_taken) / count(y.qty_taken) as 5yrAvg
  from global x
  join global y on x.species=y.species and timestampdiff(year, x.date, y.date) between 0 and 4 
  group by species    
) b using(species)
where a.species is not null 
order by a.species, a.date desc ;

That should perform faster. Let's see what Explain says about it. And is this on a shared hosting arrangement?



Edited 1 time(s). Last edit at 09/27/2017 03:40AM by Peter Brawley.