Optimize query with several joins and order by
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