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.