Yes, a shared server
Revised explain analysis:
id |1|1|2|2
select_type | PRIMARY | PRIMARY | DERIVED | DERIVED
table | a | <derived2> | x | y
type | ALL | ref | ALL |ref
possible_keys | species_c | <auto_key0> | species_c | species_c
key | NULL | <auto_key0> | NULL | species_c
key_len | NULL | 12 | NULL | 12
ref | NULL | amandaj2_possbil.a.species_code_c | NULL | amandaj2_possbil.a.species_code_c
rows | 953779 | 268 | 953779 | 268
Extra | Using where; Using filesort | NULL | Using where; Using temporary; Using filesort | Using where
The revised query returns an Error:500 from inside phpMyAdmin
From mysql command line:
mysql> SELECT
-> a.species_code_c, a.date_c, b.5_yr_avg
-> FROM fao_global AS a
-> JOIN (
-> SELECT x.species_code_c, sum(y.qty_taken) / count(y.qty_taken) AS 5_Yr_Avg
-> FROM fao_global AS x
-> JOIN fao_global AS y ON x.species_code_c = y.species_code_c
-> AND TIMESTAMPDIFF(YEAR, x.date_a, y.date_a) BETWEEN 0 AND 4
-> GROUP BY x.species_code_c
-> ) b USING (species_code_c)
-> WHERE a.species_code_c IS NOT NULL
-> ORDER BY a.date_c DESC;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 28393643
Current database: amandaj2_possbil
It ran for about 10 mins and then returned:
ERROR 2013 (HY000): Lost connection to MySQL server during query
So many thanks for the help in progressing.
Can the timeout value be extended from the client side?
I cannot think of how to reduce the size of JOIN without messing up the 5_yr_avg calculation.
The data runs across 66 years 2015-1950.
There are 1800 species.