1, the select * from tab_1 a inner join tab_2 b on Anderson, d=biggest id and b.n ame='a'
2, the select * from tab_1 a inner join tab_2 b on Anderson, d=biggest id where b.n ame='a'
The first kind is behind on writing and, the second directly write the where, I feel these two kinds of query results should be the same, are filtered out the query results, but that's a big gap, after the execution is there who can explain?
CodePudding user response:
1, the select * from tab_1 a inner join tab_2 b on Anderson, d=biggest id and b.n ame='a'If b.n ame have reasonable index on will be screening before the join,
Such as query according to the following steps:
(1) the select * from tab_2 b where b.n ame='a' into a table b
(2) the join on Anderson, d=b.i d
So, if the conditions are good and index can greatly to heavy, so this method will be a lot faster,
2, the select * from tab_1 a inner join tab_2 b on Anderson, d=biggest id where b.n ame='a'
This way is a and b of the first full table joins, then the results set to filter b.n ame='a', natural feel slow many
Theory is such, but now some of the optimizer is very powerful, can also can be reference to 1 of 2 ways to the execution of the plan