MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing query
Posted by: Jørgen Løland
Date: May 29, 2011 12:54PM

I don't think that will help because the difference between inner and outer join is that with outer joins you get null-complemented rows when there is no join match. These are the rows that needs to be filtered away by the predicate:

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

select * from t1 left join t2 on t1.i=t2.i;
i  i
1  1
2  2
3  NULL              <- a predicate to filter away this row is needed to make conversion possible

select * from t1 left join t2 on t1.i=t2.i WHERE t2.i IS NOT NULL;
i  i
1  1
2  2

That being said, there are less obvious null-rejecting predicates than in this example. Inspired by your NOT NULL column:

create table t1 (i int not null);
create table t2 (i int not null);
create table t3 (i int not null);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2);
insert into t3 values (1),(2);

select * from t1 left join t2 on t1.i=t2.i;
i	i
1	1
2	2
3	NULL

select * from t1 left join t2 on t1.i=t2.i join t3 on t2.i=t3.i;
i	i	i
1	1	1
2	2	2

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

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3711
May 23, 2011 04:12PM
1189
May 24, 2011 12:49AM
1164
May 27, 2011 08:55AM
1184
May 28, 2011 12:30AM
1170
May 28, 2011 10:40PM
Re: Optimizing query
1143
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.