Joining two fast subqueries is very slow
This pattern is very slow:

FROM ( SELECT ... )
JOIN ( SELECT ... )


especially when both subqueries have lots of rows.  This is because there are no indexes to speed up the "join".  MySQL 5.6 will create the optimal index (on the fly), but you appear to be using an older version.

Suggest upgrading.