MYSQL join on subquery optimization
What's the MySQL version?

MariaDB 10 can do the "merge" - automatically transforming the first query to the second one internally.

MySQL 5.6 cannot do that afaik, but it can materialize the derived table into a temporary one and add automatic indexes - manual.

And you could emulate that even in older versions. If you always get some code for a  then you can do 

create temporary table t_indexed as &lt;subquery&gt;;
alter tably t_indexed add key(id);

SELECT * FROM table1 t1
INNER JOIN t_indexed t2 
ON t1.id = t2.id;


If you always know the structure of the  results, you can create the temp table with this structure and proper indexes in place and use insert ... select instead.