"FROM ( SELECT ... )" is a nifty tool. But it can have a serious flaw... There are no indexes on that tmp table. Suggest you try the following...
1. Run each inner SELECT by itself to see how long it takes and how many rows it returns.
2. Decide if the tmp table it generates would work better if it had an index.
3. Try
CREATE TEMPORARY TABLE t1
SELECT ...
ALTER TABLE t1
ADD INDEX ...
and then use t1 in your big query, instead of the FROM(SELECT...).
4. If you still have issues, report back. Please include SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN. Remove the FORCE INDEX() when doing the EXPLAIN. Also bracket things with [ code ] and [ / code ] for legibility.