In plain english, basically you expected mysql to work from inner to outer parentheses but instead

   IN (SELECT species_id FROM enemy_species WHERE enemy_id = 123) 

is executed against every row in the table instead of just once, taking a REALLY long time.

I think every everyone goes through that particular mysql learning curve - use a join instead.