How did you create the indexes on the 4 tmp tables? Recommend:
CREATE TEMPORARY TABLE t (
PRIMARY KEY (joinkey)
) SELECT ...;
InnoDB or MyISAM? (It could make a big difference.) It can be controlled thus:
CREATE TEMPORARY TABLE t (
PRIMARY KEY (joinkey)
) ENGINE=InnoDB
SELECT ... ORDER BY joinkey;
(No, InnoDB won't necessarily be better.)
I think you can get by without any indexes on TTtable2. (This might save some time in building that table.)
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]