--- In firebird-support@yahoogroups.com, "Nico Callewaert" wrote:

> Hi Set,
>
> This is also frustrating sometimes. There are quite a lot of rows
> in SUBPROJ. I have a unique index defined on the fields SU_NR and
> SU_SUB. But it seems firebird is refusing to use that index. I
> don't like really so many left joins, but the customer wants to see
> al the related information in the grid.

Hi Nico!

Are you sure that index is active and that you have no other index or
key starting with SU_NR and SU_SUB as the first two fields (Firebird
at least used to be confused if having two identical indexes to choose
from)? The other parts of your query seems fine, so if you don't find
anything, post statistics from the SUBPROJ table (including all
indexes and keys).

Set

> > > FROM CSFHPX FH
> > > LEFT OUTER JOIN KLPX KL ON KL.KL_COD = FH.FH_KLNR
> > > LEFT OUTER JOIN PROJPX PR ON PR.PR_NR = FH.FH_PROJ
> > > LEFT OUTER JOIN SUBPROJ SU ON (SU.SU_NR = FH.FH_PROJ)
> > > AND (SU.SU_SUB = FH.FH_SUBPROJ)
> > > LEFT OUTER JOIN VERTEW VE ON VE.VE_COD = FH.FH_AGT
> > > LEFT OUTER JOIN TRANSPORTEUR TR ON
> > > TR.TR_CODE = FH.FH_TRANSPORTEUR
> > > LEFT OUTER JOIN WEDERVERKOPERS WE ON
> > > WE.WE_COD = FH.FH_WEDERVERKOPER
> > > WHERE (FH.FH_CODE = 'B')
> > >
> > > Plan
> > > PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (
> > > FH INDEX (FH_CODE_INDEX),KL INDEX (RDB$PRIMARY36)),
> > > PR INDEX (RDB$PRIMARY55)),SU NATURAL),
> > > VE INDEX (RDB$PRIMARY76)),TR INDEX (RDB$PRIMARY167)),
> > > WE INDEX (RDB$PRIMARY292))