On a shared server, you can't reconfigure MySQL. That's often reason enough for dumping the DB to a script you can then impoirt to a local MySQL instance that you can configure for OLAP queries.
Meanwhile, here's your Explain result made human-readable:
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; | NULL | Using where; | Using where
| Using filesort| | Using temporary;|
| Using filesort }
(To preserve text formatting in these fora, put BBCode code tags round the text, see the wikipedia page on BBCode.)
MySQL can't find an index to use for the 953,779 rows it needs, so it's doing table scans and running out of buffer memory.
The problem may be the TimeStampDiff() call, which can't use an index. Why this call? According to the table column comments, the data just specified year, and that's what you specify in the TimStampDiff() call, so why not write
... AND x.year_a-y.year_a BETWEEN 0 AND 4 ...
Then a good index to have would be (species_code_c, year_a).
Perhaps you could try that before we get more adventurous.