Many thanks for the BBCode tip - I was conscious of the poor presentation.
The revised EXPLAIN analysis:
id | select_type | table | type | possbil_keys | key | key_len | ref | rows | Extra
1 | PRIMARY | a | index | species_c | species_year_c | 33 | NULL | 962223| Using where; Using index
1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 12 | amandaj2_possbil.x.species_code_c | 280 | NULL
2 | DERIVED | x | index | species_c,species_year_c | species_c | 12 | NULL | 962223| Using where
2 | DERIVED | y | ref | species_c | species_c | 12 | amandaj2_possbil.x.species_code_c | 280 | Using where
Took me a while to realise the year_c data type needed to be changed from int(4) to varchar(6).
I was then able to create the additional indexes and modify the query to use the year_c field.
The revised query #2 returns an Error:500 from inside phpMyAdmin
From the mysql command line:
mysql> SELECT
-> a.species_code_c, a.year_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 x.year_c-y.year_c 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.year_c DESC;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 28589750
Current database: amandaj2_possbil
It seems to have returned to not completing.
It has been running for almost an hour with no result or error returned