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

Thanks very much Helen,
You made a good point, at removing the index the query time drops with 1.3
secs

Some more questions however:

1) What I don't understand, is why the Interbase SQL Optimizer takes this
index Precisely, because the field OrgType is not selective, I combined it
with the OrganisationID in the above index, because I read in a thread in
this newsgroup that this would be the way to solve the problem of an
unselective index.
Appearantly not !
Since I do not use OrgType anywhere in the query, the only reason why the
Interbase SQL Optimizer has taken this index, can be, to use an index on
OrganisationID. This is very strange, because there exists a perfect
alternative which is the RDB$Primary11 (being the index of the primary key
on OrganisationID)

2) Enthusiastic about the positive result I obtained by dropping the index,
I decided to drop some more indexes. Here again I noticed something very
strange:
At a certain moment the query time had dropped to 2.3 secs and the Plan
contained only 1 homegrown index I_Lot_Captive. This was also just like the
one above an index in which I combined the unselective field with the
primary key field to make it more selective. So I was expecting that at
dropping this index I would further lower the query time. However, wat
happened was that it increased to 3.666 secs because the Plan for some
strange reason did not include the alternative for this index which is the
RDB$Primary25 of the primary key LotID

My conclusion is: the SQL optimizer behaves in a irregular, unpredictable
and bizar incorrect way

Why is the Optimizer not able to simply take primary key indexes when they
are availlable ??? Am I correct to call the above behavior a bug ?

I need to drop the query time at least downto 1 sec
What can I do, to further improve the SQL ?

At present I have 2.3 sec with the following PLAN

PLAN JOIN (JOIN (JOIN (JOIN (TL NATURAL,L INDEX
(I_LOT_CAPTIVE,RDB$FOREIGN49),C INDEX (RDB$PRIMARY2),T INDEX
(RDB$PRIMARY28),O1 INDEX (RDB$PRIMARY11)),TB INDEX (RDB$FOREIGN79)),O2 INDEX
(RDB$PRIMARY11)),TLB INDEX (RDB$FOREIGN10,RDB$FOREIGN13))


Which goes to 3.666 secs at dropping I_Lot_Captive giving the following plan

PLAN JOIN (JOIN (JOIN (JOIN (C NATURAL,L INDEX
(RDB$FOREIGN67,RDB$FOREIGN49),O1 INDEX (RDB$PRIMARY11),TL INDEX
(RDB$FOREIGN81),T INDEX (RDB$PRIMARY28)),TB INDEX (RDB$FOREIGN79)),O2 INDEX
(RDB$PRIMARY11)),TLB INDEX (RDB$FOREIGN10,RDB$FOREIGN13))

Any input appreciated,

best regards,
Tanno


"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.2.20021103082548.076494d0@......

> 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
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>