I am trying to come up with how to order a join query to improve its performance.
Lets say we have two tables to join, to which some filters must be applied.
Is it the same to do:
table1_result = select * from table1 where field1 = 'A'; table2_result = select * from table2 where field1 = 'A'; result = select * from table1 as one inner join table2 as two on one.field1 = two.field1;
to doing this:
result = select * from table1 as one inner join table2 as two on one.field1 = two.field1 where one.field1 = 'A' and two.field1 = 'A';
or even doing this:
result = select * from table1 as one inner join table2 as two on one.field1 = two.field1 and one.field1 = 'A';
Thank you so much!!
Some common optimization techniques to improve your queries are here:
Indexthe columns used in joining. If they are
foreign keys, normally databases like
MySqlalready index them.
*and explicitly select the columns that you really need.
- The order of joining in most of the cases won’t matter, because
DB-Enginesare inteligent enough to decide that.
So its better to analyze your structure of both the joining tables, have indexes in place.
And if anyone is further intrested, how changing
conditions order can help getting the better performance. I’ve a detailed answer over here mysql Slow query issue.
Answered By – Imran Zahoor
Answer Checked By – Willingham (BugsFixing Volunteer)