Thanks..
I am also assuming that that order that an index in I.E. CUSTNO+PIN or
PIN + CUSTNO effects the performance of an ORDER BY ??

Gordon Hamm
Voice Data Systems Inc.
435-635-7464

----- Original Message -----
From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>
To: <IBOBJECTS@...>
Sent: Friday, July 20, 2001 2:42 AM
Subject: Re: [IBO] Interbase index question....


> >so, would it be best to create 5 different indexs since I am going to use
> >these indexes commonly ??
> >I.E.
>
> >CUSTID
> >CUSTID+PIN
> >CUSTID+ANI
> >PIN
> >ANI
>
> No, that's too many indexes. If you have an index (PIN, CUSTID) that will
> cover both CUSTID+PIN and only PIN. Which indexes you ought to create
> depends on how often each value of a given field exists in the table. If
> they are relatively rare, I would only use three indexes - one on CUSTID,
> one on (PIN, CUSTID) and one on (ANI, CUSTID). If even combinations have
> plenty of repetitions, you should add to your index the primary key as the
> last field to make each entry in the index as unique as possible (too much
> repetitions may make updates slow). Also, you should think of how often
you
> issue each of your calls. I find that in Interbase it is more often a
> problem of too many indexes than too few. E.g. if you issued
>
> SELECT * FROM CALL_LOG WHERE CUSTID='123456' AND PIN='1235'
>
> and had a separate index on each of the fields only (i.e. no combined
(PIN,
> CUSTID) index, then Interbase would use two indexes and that would slow
> down your query a lot. In such circumstances I do the following to reduce
> to only using one index:
>
> SELECT * FROM CALL_LOG WHERE CUSTID='123456' AND (PIN='1235' or 2=3)
>
> This makes it impossible to use an index for PIN.
>
> HTH,
> Set
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>