At 04:57 PM 02-11-02 +0100, Tanno Vorenkamp wrote:

>Hi All,
>
>The following select query takes 4 seconds
>Even though all ID's used in the Joins and all fields used in the where
>conditions have an index
>
>Does anyone know how to improve this ?
>
>SELECT
>t.TenderID,
>l.OrganisationID AS SellerOrgID,
>o1.Name AS SellerOrgName,
>tb.Organisationid as BidOrgID,
>o2.Name AS BidOrgName,
>l.Captive,T.EndDate,
>c.CarID,c.Licenseno,
>l.Price,
>tlb.BidPrice as CarBidPrice,
>c.Brand,c.Model,c.CarType,c.Fuel,c.CarDate,
>c.Kilometers from
>Tender t join TenderLot tl on t.Tenderid = tl.Tenderid
>join Lot l on tl.lotid = l.lotid
>join Organisation o1 on l.organisationid = o1.organisationid
>join Car c on l.carid = c.carid
>left outer join Tenderbid tb
>on ((t.tenderid = tb.tenderid) and (tb.highestbid = 1))
>left outer join Organisation o2
> on tb.organisationid = o2.organisationid
>left outer join Tenderlotbids tlb on ((l.lotid = tlb.lotid)
> and (tlb.bidorgid = tb.organisationid))
>WHERE
>(L.LotState = 3
>AND l.OrganisationID = 1598
>AND T.EndDate >= 37550
>AND L.Terminated = 0)
>
>The Interbase Query Plan is
>
>OIN (JOIN (JOIN (JOIN (C NATURAL,L INDEX
>(I_LOT_LOTSTATE,RDB$FOREIGN67,RDB$FOREIGN49),O1 INDEX
>(I_ORGANISATION_ORGTYPE),TL INDEX (RDB$FOREIGN81),T INDEX
>(RDB$PRIMARY28,I_TENDER_ENDDATE)),TB INDEX
>(I_TENDERBID_HIGHESTBID,RDB$FOREIGN79)),O2 INDEX
>(I_ORGANISATION_ORGTYPE)),TLB INDEX (RDB$FOREIGN10,RDB$FOREIGN13))

Tanno,

Look at the index I_ORGANISATION_ORGTYPE on your Organisation table. If it
has but few possible values throughout the table then you will do better by
dropping that index, because of low selectivity.

heLen