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!!

CodePudding user response：

Some common optimization techniques to improve your queries are here:

• Index the columns used in joining if they are foreign keys, normally databases like MySql already index them.
• Index the columns used in conditions or WHERE clause.
• Avoid * and explicitly select the columns that you really need.
• The order of joining in most of the cases won't matter, because DB-Engines are 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.