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))

Any input appreciated

regards,
Tanno