Did you set this suitable large before the CREATE?

SET max_heap_table_size = ...;

15 seconds to read 4 million rows is not necessarily "slow".

If you had the slowlog turned on, "Rows examined" would be an interesting number.

Your table ass_ needs this composite index (in any order):

INDEX(AssociatedObjectClass, AssignmentSubType, IsDeleted)

If that is not sufficient, please provide the new EXPLAIN and SHOW CREATE TABLE for each table.

Did you set this suitable large before the CREATE?

SET max_heap_table_size = ...;

15 seconds to read 4 million rows is not necessarily "slow".

If you had the slowlog turned on, "Rows examined" would be an interesting number.

Your table ass_ needs this composite index (in any order):

INDEX(AssociatedObjectClass, AssignmentSubType, IsDeleted)

If that is not sufficient, please provide the new EXPLAIN and SHOW CREATE TABLE for each table.

More

Since the Optimizer seems determined to start with existsTT, my index is not useful.

(Please don't reuse the alias existsTT; it is confusing.)

If the IDs in TTT97a84e6f7eaa4bf3b2fbb8491517324c are distinct, please replace its index with PRIMARY KEY(ID).

Many columns are NULLable. And the EXPLAIN seems to be doing tests on such. Consider changing most columns to be NOT NULL