0

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.

1 Answer 1

0

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.

11
  • I am on MYSQL 5.6. And, no, its not always the same code I get - the subquery is generated based on user input which I pass into the library I don't control.
    – pathikrit
    Oct 26, 2015 at 20:58
  • Then check the EXPLAIN for your query (or both of them) to see how it is executed.
    – jkavalik
    Oct 26, 2015 at 20:59
  • I understand the code is not the same, but does it always select from the same table or does it add some joins sometimes etc? If the library gives you the subquery as text, you can use that in many statements.
    – jkavalik
    Oct 26, 2015 at 21:01
  • Unfortunately, the subquery can have other joins and filters insider. I will give Maria a try.
    – pathikrit
    Oct 26, 2015 at 21:19
  • 1
    "based on user input" = "asking for trouble"
    – Rick James
    Dec 7, 2015 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.