MySQL Forums
Forum List  »  Performance

Optimize query with several joins and order by
Posted by: Christopher Cudennec
Date: July 12, 2007 06:33AM

Hi there,

I have a hard time optimizing the following query. It was generated by Hibernate - so getting an optimized version to run with Hibernate will be my next task ;). The combination of those inner joins and the order by slows the query down. this_.id and all those xyz_ref attributes are primary keys. There is one index for _DISRCRIM as well.

select distinct this_.id as y0_
from bo_DocumentContainer this_
inner join bo_Associate this_1_ on this_.id=this_1_.documentContainer_ref
inner join bo_Party this_2_ on this_.id=this_2_.associate_ref
inner join bo_Person this_3_ on this_.id=this_3_.party_ref
left outer join bo_System this_4_ on this_.id=this_4_.person_ref
inner join bo_Role currentrol1_ on this_.id=currentrol1_.associate_ref
left outer join bo_AccountingRole currentrol1_1_ on currentrol1_.id=currentrol1_1_.role_ref
where this_._DISCRIM in ('foo.bar.Person', 'foo.bar.System')
and currentrol1_._DISCRIM='foo.bar.CustomerRole'
and not (this_2_.currentState in ('initialParty', 'deletedParty'))
and this_3_.originalPerson_ref is null order by this_.id desc limit 10

This is the output of EXPLAIN:
1, 'SIMPLE', 'this_3_', 'ref', 'PRIMARY,FKFC8D77A71C74F70E,FKFC8D77A71E5007F1', 'FKFC8D77A71E5007F1', 5, 'const', 6695, 'Using where; Using index; Using temporary; Using filesort'
1, 'SIMPLE', 'this_', 'eq_ref', 'PRIMARY,DefaultDocumentContainer_discrim', 'PRIMARY', 4, 'tds.this_3_.party_ref', 1, 'Using where'
1, 'SIMPLE', 'this_2_', 'eq_ref', 'PRIMARY,FK5AB86C54D3351AF9', 'PRIMARY', 4, 'tds.this_.id', 1, 'Using where; Distinct'
1, 'SIMPLE', 'this_1_', 'eq_ref', 'PRIMARY,FKFE6DF98CF33AFD59', 'PRIMARY', 4, 'tds.this_.id', 1, 'Using index; Distinct'
1, 'SIMPLE', 'this_4_', 'eq_ref', 'PRIMARY,FK2C64E81B5957ED7', 'PRIMARY', 4, 'tds.this_.id', 1, 'Using index; Distinct'
1, 'SIMPLE', 'currentrol1_', 'ref', 'Role_discrim,FK2EE4948E39A16B1', 'FK2EE4948E39A16B1', 5, 'tds.this_.id', 1, 'Using where; Distinct'
1, 'SIMPLE', 'currentrol1_1_', 'eq_ref', 'PRIMARY,FK7814EFD6368EEF4', 'PRIMARY', 4, 'tds.currentrol1_.id', 1, 'Using index; Distinct'

Are there any recommendations?

Thanks,

Christopher

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize query with several joins and order by
2025
July 12, 2007 06:33AM


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.