MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing query
Posted by: Jørgen Løland
Date: May 28, 2011 12:30AM

@Rick: Outer joins can be transformed into inner joins if there is a null-rejecting predicate on one of the inner tables. Example:

create table t1 (i int);
create table t2 (i int);
insert into t1 values (1),(2);
insert into t2 values (1),(2);

explain extended select * from t1 left join t2 on t1.i=t2.i;
...
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i` from `test`.`t1` left join `test`.`t2` on((`test`.`t1`.`i` = `test`.`t2`.`i`)) where 1

explain extended select * from t1 left join t2 on t1.i=t2.i where t2.i is not null;
...
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t1`.`i` is not null))

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3461
May 23, 2011 04:12PM
1103
May 24, 2011 12:49AM
1092
May 27, 2011 08:55AM
Re: Optimizing query
1091
May 28, 2011 12:30AM
1106
May 28, 2011 10:40PM
1057
May 29, 2011 12:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.