We are considering using Hibernate as a persistence layer to our web application. It seems to only want to do outer joins and this concerns me b/c they can be expensive. I created the following benchmark experiment and learned that the explain plan for the two constrained queries is the same.
What I would like to know is can I depend on the performance being the same, or is the optimizer doing something different b/c of the outer join? I seem to remember something about it not using the index all the time or forcing a full table scan in some cases. Since Hibernate seems to using only an outer join rather than a join, I would like this concern put to rest. Thanks for any insight. Boyd create table foo ( foo_id int unsigned not null auto_increment primary key, foo_sn varchar(15), ) ; create table foo_child ( foo_child_id int unsigned not null auto_increment primary key, foo_id int unsigned not null, foo_child_sn varchar(15), index fk_foo$foo_child (foo_id) ) ; insert into foo values (1,'a'), (2,'b'), (3,'c'), (4,'d') ; insert into foo_child values (1,1,'z'), (2,1,'y'), (3,2,'x'), (4,3,'w'), (5,9,'v bad 1'), (6,9,'v bad 2'), (7,3,'t'), (8,4,'s') ; -- unconstrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id select * from foo_child fc left join foo f on fc.foo_id = f.foo_id -- constrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 select * from foo_child fc left join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405
