MYSQL join on subquery optimization

join;MySQLoptimizationsubquery

This is a distilled version of the query that I am trying to run:

SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 
ON t1.id = t2.id;

The above query is incredibly slow because MYSQL fails to notice that the subquery in the join i.e. SELECT * FROM table2 WHERE foo = 'bar' has id as a primary key which it can use in the join clause.

Ofcourse, I should have written this query as:

SELECT * FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id 
WHERE t2.foo = 'bar';

But, the problem is that the subquery(SELECT * FROM table2 WHERE foo = 'bar') is generated by a library that I do not control. What is the best way for me to optimize this given that I cannot do much about the subquery since I programmatically get it from somewhere else.

Best Answer

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 <subquery>;
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.

Related Question