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.