Boyd,
You can tell Hibernate not to use outer-joins by setting hibernate.use_outer_join to false in the hibernate configuration properties file.
It's an always-never proposition. Of course, you can code your own queries using the Hibernate Query object to write your own when you know you do need one (and you still get the benefit of the relational-object mapping).
I won't answer the question about the Innodb optimizer, as I don't know the answer.
David
Boyd E. Hemphill wrote:
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
